同一查询中的 PostgreSQL nextval 和 currval [英] PostgreSQL nextval and currval in same query

问题描述

我希望调用一次 NEXTVAL 并多次使用返回的值.但是它不起作用.

I was hoping to call NEXTVAL once and use the returned values multiple times. However it doesn't work.

假设序列 seq_name 现在是 10(查询是独立执行的，而不是按列出的顺序):

Assume the sequence seq_name is at 10 now (queries are executed independently, not in listed order):

SELECT NEXTVAL('seq_name'), NEXTVAL('seq_name');

上面会给出 11、12(我再次想要 11 和 11).

The above will give 11, 12 (I want 11 and 11 again).

SELECT NEXTVAL('seq_name'), CURRVAL('seq_name');
SELECT NEXTVAL('seq_name'), LASTVAL();

以上将给出 11, 10 OR 给出在当前会话中从未调用过 NEXTVAL 的错误.[ 我知道错误发生的原因:) ]

The above will give 11, 10 OR give an error that NEXTVAL was never called in the current session. [ I know why the error happens :) ]

有没有办法在同一个 SQL 语句中使用 NEXTVAL 和 CURRVAL 来获得相同的递增值(在本例中为 11)以供重用?或者这个问题的简单解决方案?

Is there a way to use NEXTVAL and CURRVAL in the same SQL statement to get the same incremented value (11 in this case) for reuse? Or a simple solution to this problem?

推荐答案

一个可能的解决方案

SELECT nextval nextval1, nextval nextval2
  FROM
(
  SELECT NEXTVAL('seq_name') nextval
) q

这是 SQLFiddle 演示.

Here is SQLFiddle demo.

UPDATE 要插入而不是 INSERT INTO ... VALUES 使用 INSERT INTO ... SELECT

UPDATE To insert instead of INSERT INTO ... VALUES use INSERT INTO ... SELECT

INSERT INTO Table1 (col1, col2, col3, col4, ...) 
SELECT nextval, nextval, 5, 'Text value', ...
  FROM
(
  SELECT NEXTVAL('seq_name') nextval
) q

这篇关于同一查询中的 PostgreSQL nextval 和 currval的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！