Can I use return value of INSERT...RETURNING in another INSERT?

Question

Is something like this possible?

INSERT INTO Table2 (val)
VALUES ((INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id));

like using the return value as value to insert a row in a second table with a reference to the first table?

Answer 1

You can do so starting with Postgres 9.1:

with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val)
SELECT id
FROM rows

In the meanwhile, if you're only interested in the id, you can do so with a trigger:

create function t1_ins_into_t2()
  returns trigger
as $$
begin
  insert into table2 (val) values (new.id);
  return new;
end;
$$ language plpgsql;

create trigger t1_ins_into_t2
  after insert on table1
for each row
execute procedure t1_ins_into_t2();

Answer 2

The best practice for this situation. Use RETURNING … INTO.

INSERT INTO teams VALUES (...) RETURNING id INTO last_id;

Note this is for PLPGSQL

Answer 3

DO $$
DECLARE tableId integer;
BEGIN
  INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id INTO tableId;
  INSERT INTO Table2 (val) VALUES (tableId);
END $$;

Tested with psql (10.3, server 9.6.8)

Answer 4

In line with the answer given by Denis de Bernardy..

If you want id to be returned afterwards as well and want to insert more things into Table2:

with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val, val2, val3)
SELECT id, 'val2value', 'val3value'
FROM rows
RETURNING val

Answer 5

You can use the lastval() function:

Return value most recently obtained with nextval for any sequence

So something like this:

INSERT INTO Table1 (name) VALUES ('a_title');
INSERT INTO Table2 (val)  VALUES (lastval());

This will work fine as long as no one calls nextval() on any other sequence (in the current session) between your INSERTs.

As Denis noted below and I warned about above, using lastval() can get you into trouble if another sequence is accessed using nextval() between your INSERTs. This could happen if there was an INSERT trigger on Table1 that manually called nextval() on a sequence or, more likely, did an INSERT on a table with a SERIAL or BIGSERIAL primary key. If you want to be really paranoid (a good thing, they really are you to get you after all), then you could use currval() but you'd need to know the name of the relevant sequence:

INSERT INTO Table1 (name) VALUES ('a_title');
INSERT INTO Table2 (val)  VALUES (currval('Table1_id_seq'::regclass));

The automatically generated sequence is usually named t_c_seq where t is the table name and c is the column name but you can always find out by going into psql and saying:

=> \d table_name;

and then looking at the default value for the column in question, for example:

id | integer | not null default nextval('people_id_seq'::regclass)

FYI: lastval() is, more or less, the PostgreSQL version of MySQL's LAST_INSERT_ID. I only mention this because a lot of people are more familiar with MySQL than PostgreSQL so linking lastval() to something familiar might clarify things.

Answer 6

table_ex

id default nextval('table_id_seq'::regclass),

camp1 varchar

camp2 varchar

INSERT INTO table_ex(camp1,camp2) VALUES ('xxx','123') RETURNING id