2

I want to be able to use the current value of an auto ID by giving it some kind of name and just referring to it (instead of calling SELECT for each record):

Requests table has a serial column named requestid.

insert into testschema.requests (chid) values
(1);

I want to use select currval() multiple times without having to use SELECT for each line:

insert into testschema.requestinfo (requestid, reportid, amount) values
((select currval('testschema.requests_requestid_seq')), 10, 40),
((select currval('testschema.requests_requestid_seq')), 15, 29),
((select currval('testschema.requests_requestid_seq')), 40, 4);

I've tried placing the select on its own line with no success:

SELECT currval('testschema.requests_requestid_seq') as reqid;
INSERT INTO testschema.requestinfo(requestid, reportid) VALUES
 (reqid, 2);

Bottom line: I want to refer to the currval without having to use SELECT currval() for each line.

2 Answers 2

3

You would not need a subquery in your statement. The bare function does the job:

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
VALUES (currval('testschema.requests_requestid_seq'), 10, 40)
     , (currval('testschema.requests_requestid_seq'), 15, 29)
     , (currval('testschema.requests_requestid_seq'), 40, 4);

But currval() only makes sense if you have (implicitly or explicitly) called nextval() for the same sequence before in your session. In this case, and if you have not called nextval() for any other sequences in the meantime, you can simplify with lastval().

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
VALUES (lastval(), 10, 40)
     , (lastval(), 15, 29)
     , (lastval(), 40, 4);

Either way, if you have many rows like this, look to @dezso's answer for a way to avoid typing the function repeatedly.

currval() and lastval() are volatile functions:

SELECT proname, provolatile
FROM   pg_proc
WHERE  proname IN ('currval', 'lastval');
proname provolatile
currval v
lastval v

Meaning they will still be evaluated for every row that way.
To also avoid repeated execution of the function, move it to a separate CTE or subquery and CROSS JOIN to your input values:

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
SELECT id, v.*
FROM   lastval() id
CROSS  JOIN (VALUES (10,40), (15,29), (40,4)) v;

fiddle

3
  • Thanks, I keep forgetting about lastval()... Commented Jul 11, 2014 at 7:48
  • 1
    @Erwin Brandstetter . I can not replicate your last code. here is my demo: dbfiddle.uk/v5vpvO_t
    – jian
    Commented Apr 11, 2023 at 6:46
  • Thanks for pointing out @jian! I fixed the syntax error. Commented Apr 11, 2023 at 16:28
1

Try

WITH vals (reportid, amount) AS (
    VALUES (10, 40),
           (15, 29),
           (40, 4)
)
INSERT INTO testschema.requestinfo (requestid, reportid, amount) 
SELECT currval('testschema.requests_requestid_seq'), reportid, amount
FROM vals;

This is a nice generic solution for similar tasks. It neatly separates the fixed values from the dynamically obtained ones.

With big data sets I would swap the VALUES 'table' with a real temporary table, though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.