By RockNinja


2014-08-28 14:36:45 8 Comments

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?

1 comments

@Erwin Brandstetter 2014-08-28 15:27:22

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 Questions

Sponsored Content

1 Answered Questions

3 Answered Questions

[SOLVED] Bulk-Insert Select performance over individual Inserts

2 Answered Questions

1 Answered Questions

[SOLVED] Behaviour of Bulk Insert in pgsql or any Database

1 Answered Questions

Sponsored Content