Bulk insert in multiple tables

Question

I have two tables on which I perform some bulk inserts:

1. key: key_id (pk), key_name
2. related_key: related_key_id (pk), key_id (fk)

First INSERT:

values_data = "($$key_1$$), ($$key_2$$)"
INSERT INTO key (key_name) VALUES values_data

Sec INSERT:

values_data = "(1, `the id of the first value inserted in key`)
 , (1, `the id of the sec value inserted in key`)"
INSERT INTO related_key (related_key_id, key_id) VALUES values_data

I don't know how to do the second INSERT because I don't know how to get those ids for the rows inserted in the key table.

Or is there a better way of doing this?

Answer 1

Use a data-modifying CTE to chain the inserts in a single statement.

Assuming from your example that you want to insert 1 for related_key_id in all rows.

WITH ins_key AS (
 INSERT INTO key (key_name)
 VALUES ($$key_1$$)
 , ($$key_2$$)
 RETURNING key_id -- return newly generated key_id(s)
 )
INSERT INTO related_key (related_key_id, key_id) 
SELECT 1, key_id
FROM ins_key;