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?
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
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;
You can try this:
INSERT INTO secondTable (name, foreign_id) VALUES ('someexternalname', (SELECT
MAX (id) FROM firstTable))
