PostgreSQL nextval and currval in same query

postgresql

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

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

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

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

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

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 🙂 ]

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?

Best Answer

A possible solution

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

Here is SQLFiddle demo.

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
Related Topic