13

In PostgreSQL, it is possible to put RETURNING at the end of an INSERT statement to return, say, the row's primary key value when that value is automatically set by a SERIAL type.

Question:

How do I store this value in a variable that can be used to insert values into other tables? Note that I want to insert the generated id into multiple tables. A WITH clause is, as far as I understand, only useful for a single insert. I take it that this will probably have to be done in PHP.

This is really the result of bad design; without a natural key, it is difficult to grab a unique row unless you have a handle on the primary key;

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Adam
  • 3,668
  • 6
  • 30
  • 55
  • What language and client library are you using? Are you trying to do this with `psql`? – Craig Ringer Mar 26 '13 at 01:32
  • I'm trying to do this in a .sql script, which, as I understand it, can contain plsql. If that is what's necessary, then I'll have to learn it. – Adam Mar 26 '13 at 03:15
  • PL/PgSQL can be used inside `DO` blocks and `CREATE [OR REPLACE] FUNCTION`. PostgreSQL doesn't support plsql, so you can't use that. If you intend to run the `.sql` script with `psql` you can use `psql`'s backslash commands like `\copy` and `\set`, but these are part of the `psql` client not the server back-end. Please take a look at the PostgreSQL tutorial. – Craig Ringer Mar 26 '13 at 03:21
  • To be honest, I think this is also the result of bad design; relying entirely on the primary key can result in running into issues with losing handles on your data because of a lack of natural keys. – Adam May 05 '13 at 14:21

1 Answers1

27

... that can be used to insert values into other tables?

You can even do that in a single SQL statement using a data-modifying CTE:

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

Requires PostgreSQL 9.1 or later.

db<>fiddle here (Postgres 13)
Old sqlfiddle (Postgres 9.6)

Reply to 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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228