Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

postgresql

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

like image 768
Lecko Avatar asked Dec 11 '14 11:12

Lecko


2 Answers

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.

like image 173
a_horse_with_no_name Avatar answered Nov 11 '22 12:11

a_horse_with_no_name


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.

like image 26
Denis de Bernardy Avatar answered Nov 11 '22 12:11

Denis de Bernardy