1

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.

3

First of all, 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);

Next, currval() only makes sense if you have (implicitly or explicitly) called nextval() for the same sequence before in your session. 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')

Result:

proname provolatile
currval v
lastval v

That means, 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 *           -- order of rows is maintained from left to right
FROM   lastval()   -- or currval('testschema.requests_requestid_seq')
     , VALUES (10, 40), (15, 29), (40, 4);
  • Thanks, I keep forgetting about lastval()... – dezso Jul 11 '14 at 7:48
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, privacy policy and cookie policy

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