Save return values from INSERT...RETURNING into temp table (PostgreSQL)

Question

I have a table table1 with columns id,value1 and value2.

Also I have a query

INSERT INTO table1(value1,value2) SELECT value3,value4 FROM table2 RETURNING id

that returns set of ids. I want to store return values (these ids) in some temp table. Something like that:

INSERT INTO TEMP temp1 INSERT INTO table1(value1,value2) SELECT value3,value4 FROM table2 RETURNING id

How can I do it?

DBMS is PostgreSQL

user330315 · Accepted Answer · 2014-12-11 11:28:30Z

22

with inserted as (
  INSERT INTO table1 (value1,value2) 
  SELECT value3,value4 
  FROM table2 
  RETURNING id
) 
insert into temp
select id 
from inserted;

This requires Postgres 9.2 or later.

answered Dec 11, 2014 at 11:28

user330315

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

1 Comment

granadaCoder Oct 3 at 12:46

for future readers : this is : "Common Table Expressions" aka "CTE"s see -> postgresql.org/docs/current/queries-with.html

Denis de Bernardy · Accepted Answer · 2014-12-11 11:33:56Z

5

Two options.

If you need it just for one follow-up query, a with statement (see the horse's answer) is the easiest.

If you need it for more than one follow-up query, the other option is to not use insert ... returning, but rather create table as:

CREATE TEMPORARY TABLE foo AS
SELECT value3,value4 FROM table2

Caveats: if necessary, create the indexes you need on the table -- and analyze it if you do.

answered Dec 11, 2014 at 11:33

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

1 Comment

granadaCoder Oct 3 at 12:28

for future readers : "a with statement" : postgresql.org/docs/current/queries-with.html "Common Table Expressions" aka "CTE"s is the other name for it. upvote for the 2 reasons given here for "one time" or "later re-use"

Collectives™ on Stack Overflow

Save return values from INSERT...RETURNING into temp table (PostgreSQL)

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related