Postgres insert with select and values

Question

So I'm trying to insert a value into a secondary table, get the returned id from the insert, and use it together with some values in a prepared statement I'm writing.The return of the first statement(table2) is an id for a foreign key column in the first table. I want to get the id of table 1 in the end.Something like :

WITH table2ID AS
(
INSERT INTO table2 (value) VALUES ('somevalue') RETURNING id;
)
INSERT INTO table1(table2returnvalue,othervalue) VALUES
(table2ID 'val2') RETURNING id

I can see that I will probably need a transaction as well, because I don't want an isolated in table2 if table1's statement fails for some reason.

Could you please help?

What exactly is your question? A single statement (and that's what you have) is always atomic. It either succeeds completely or fails completely. — user330315
– user330315, Commented Feb 17, 2015 at 17:18
As I specified I'm trying to use the return value from an earlier insert as a value for the next insert statement — user3453202
– user3453202, Commented Feb 17, 2015 at 17:35

user1322772 · Accepted Answer · 2015-02-17 17:47:32Z

2

To do this with a CTE its usually easiest to return the entire row (output from table2) and reference that in your second insert query.

WITH table2 AS
(
    INSERT INTO table2 ( value )
    VALUES ( 'somevalue' )
    RETURNING table2.*;
)
INSERT INTO table1 ( table2returnvalue, othervalue )
SELECT table2.id, 'val2'
FROM table2
RETURNING table1.id

answered Feb 17, 2015 at 17:47

user1322772

Sign up to request clarification or add additional context in comments.

1 Comment

user3453202 Over a year ago

Thank you, this was a great help!

Frank Heikens · Accepted Answer · 2015-02-17 17:47:02Z

1

I think you're looking for something like this:

WITH table2ID AS
(
  INSERT INTO table2 (value) VALUES ('somevalue') RETURNING id;
)
INSERT INTO table1(table2returnvalue,othervalue) 
SELECT table2ID.id, 'val2' FROM table2ID;

answered Feb 17, 2015 at 17:47

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

Postgres insert with select and values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related