CODEWITHSUNDEEP
sqlpostgresqltransactionsforeign-keysrelational-database
Victor Ferreira
Victor Ferreira

Reputation: 6449

Referencing inserted ID in multiple insert transactions in Postgres

I need to build a SQL query that must be able to insert data in a first table, grab the inserted ID and then use it as foreign key in the following tables.

WITH inserted AS (
    INSERT INTO firstTable (name) VALUES ('somename') RETURNING id
)

SELECT * FROM inserted; -- this has the inserted id

INSERT INTO secondTable (name, foreign_id) VALUES ('someexternalname', ???)

So how do I reference the id in inserted in the secondTable insert?

Upvotes: 2

Views: 2149

Answers (3)

Shawn.X
Shawn.X

Reputation: 1363

You have completed this 80% percent, the complete SQL is:

with inserted as (
 insert into first_table(name) values ('somename') returning id
)
insert into second_table(name, foreign_id) select 'someexternalname',id from inserted

Upvotes: 3

nunoq
nunoq

Reputation: 550

You can do this:

WITH inserted AS (
    INSERT INTO firstTable (name) VALUES ('somename') RETURNING id
)

INSERT INTO secondTable (name, foreign_id)
SELECT
'someexternalname',
id
FROM inserted;

Upvotes: 2

Pallavi Sharma
Pallavi Sharma

Reputation: 140

You can try this:

INSERT INTO secondTable (name, foreign_id) VALUES ('someexternalname', (SELECT 
MAX (id) FROM firstTable))

Upvotes: 0

Related Questions