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?

Kuroshio

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();

Trautman

How to insert values beside the returning id ? for example: INSERT INTO TABLE2(val1, val2, val3) (1, 2, SELECT id FROM rows) – Skell

@MahmoudHanafy: replacing rows with (some_query returning ...) might work nowadays (haven't tried). – Trautman

@MahmoudHanafy: To insert values beside the returning id you can do something like this: INSERT INTO TABLE2(val1, val2, val3) SELECT id, 1, 2 FROM rows – Thrifty

upvoted! is this atomic meaning if first insert succeeds and second one doesnt what happens then? – Ausgleich

@Ausgleich Just tested! v12.4. First INSERT is indeed roll-backed if the second one fails, but the series/autoincrement of the first INSERT is skipped – Ferula

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

Decedent

Is this actually a thing? No part of the doc you linked to seems to mention RETURNING ... INTO. – Synergistic

@Alec: I found this documentation in this answer. – Iffy

@PedroD: It does. – Iffy

Very useful. In case it's not clear to other readers, you can also RETURNING col1, col2 INTO var1, var2 – Zephaniah

@Synergistic Wow. Finally. I just spent the past 3 hours looking for this answer, whicch ought to be marked as the correct one. I just want to store it in a variable to be reused. The whole WITH clause was far too complicated for what I was trying to achieve – Misalliance

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)

Insecure

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

Thrifty

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.

Grapeshot

Better use currval(), though, in case a trigger on table1 does subsequent inserts. – Trautman

@Denis: True, but then you need the name of the sequence. I'll add a little update to that effect just to cover all the bases. – Grapeshot

LASTVAL() and CURRVAL() both work for the current database connection, not for other connections. Other users can update the sequence at the samen time, that won't change your results. Don't worry about others, they will never change your results for LASTVAL and/of CURRVAL. LASTVAL and CURRVAL can't be used at all when using a connectionpool without a TRANSACTION, that's when things go wrong: You don't control the database connection. – Bosson

@Frank: Yes, they're all session specific but the problem with lastval is that there could be a sequence based INSERT behind your back from an AFTER INSERT trigger on Table1. That would be in the current session and would, presumably, change lastval() when you're not expecting it. – Grapeshot

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 

Nibelungenlied