2

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?

5

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.