How to Perform Bulk Insert in Multiple Tables in PostgreSQL

bulk-insertctepostgresqlpostgresql-9.3

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?

Best Answer

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;
Related Question