Geek asks and answers

Save and reuse the value returned by INSERT ... RETURNING

In PostgreSQL, it is possible to put RETURNINGat the end INSERTto return, say, the value of the primary key of a string when that value is automatically set by the type SERIAL.

Question:

How to save this value in a variable that can be used to insert values ​​into other tables? Please note that I want to insert the generated idinto several tables. The sentence WITH, as I understand it, is only useful for one insert. I believe this should probably be done in PHP.

This is really the result of poor design; without a natural key, it is difficult to get a unique string if you do not have a primary key descriptor;

+6
source share
1 answer

... what can be used to insert values ​​into other tables?

You can even do this in a single SQL statement using a CTE modifying the data :

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('foo')
   RETURNING tbl1_id
   )
INSERT INTO tbl2(tbl1_id)
SELECT * FROM ins1

PostgreSQL 9.1 or later is required.

dB <> fiddle here (Postgres 11)
sqlfiddle (Postgres 9.6)

Answer the question update

You can also insert into multiple tables in a single query:

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('bar')
   RETURNING tbl1_id
   )
 , ins2 AS (
   INSERT INTO tbl2(tbl1_id)
   SELECT tbl1_id FROM ins1
   )
INSERT INTO tbl3(tbl1_id)
SELECT tbl1_id FROM ins1;
+16
source

More articles:


All Articles