CODEWITHSUNDEEP
postgresqlsql-insert
ceving
ceving

Reputation: 23814

How to nest `insert ... returning`?

Postgresql can return auto incremented values with returning:

insert into table1 (id) values (null) returning id;

I tried to insert the returned value into another table:

insert into table2 (id) values ((insert into table1 (id) values (null) returning id)) returning id;

But this throws an syntax error before into in the nested insert.

How to use the return value of the inner insert as a value for the outer insert?

Upvotes: 2

Views: 302

Answers (1)

user330315
user330315

Reputation:

You can chain data modifying CTEs:

with new_t1 as (
  insert into table1 (id) values (default) 
  returning id
)
insert into table2 (id) 
select id 
from new_t1;

Note that insert into table1 (id) values (null) returning id would return null as you explicitly ask to insert NULL into that column.

To make sure that a value is generated, you need to tell Postgres to use the default value for that column, not a NULL value.

Upvotes: 6

Related Questions