CODEWITHSUNDEEP
sqlpostgresql
Jamaal
Jamaal

Reputation: 1489

How do I insert multiple rows with a foreign key using a CTE in Postgres?

I want to do a bulk insert transaction but I'm not too sure how to do this using CTEs or a more efficient method. This is what I have so far:

with bulky as (
    insert into products (title, description, price) 
                  values ('Dope product 1', 'Buy diz', 9.99), 
                         ('Dope product 2', 'Buy diz', 8.99), 
                         ('Dope product 2', 'Buy diz', 7.99) 
                  returning id
) 
insert into product_metadata (product_id, sales_volume, date) 
                       values (???, 80, '2017-03-21'), 
                              (???, 50, '2017-03-21'), 
                              (???, 70, '2017-03-21');

The problem with my CTE is I don't know how to get the individual ids from the first insert statement to be inserted to their corresponding records for the second statement which has the 'product_id' foreign key.

How would I construct the statement to make it work? I'm open to alternative solutions that offer a more efficient method to achieve the same result.

Upvotes: 5

Views: 4176

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The problem is even when you get the id from the first insert using returning id there is no way you can use it to insert on the same order, because set in db are by definition unsorted.

I did the exercise to match your requirement, but you should rethink your requirement because this is very tricky. Specially the part where you need a field to know the order for metadata.

SQL DEMO

  1. You insert and get the id, in this case you get {1,2,3} but you can get {31,32,33}

  2. Now using row_number you order that result by id and asign an rn to know the insert order {1,2,3}

  3. Now your metadata also need some field so you know the order. In this case I create a field insert_order to match the order in which you are inserting. {100,200,300}

  4. Also create a rn for metadata

  5. Finally you can join both tables because you know the order of both.

.

with prod as (         
    insert into products (title, description, price) 
                  values ('Dope product 1', 'Buy diz', 9.99), 
                         ('Dope product 2', 'Buy diz', 8.99), 
                         ('Dope product 2', 'Buy diz', 7.99) 
                  returning id
), prod_order as (
       SELECT id, row_number() over (order by id) as rn
       FROM prod
), metadata as (
    SELECT *, row_number() over (order by insert_order) as rn
    FROM (values (100, 80, '2017-03-21'::timestamp),
                 (200, 50, '2017-03-21'::timestamp), 
                 (300, 70, '2017-03-21'::timestamp)
          ) t (insert_order, sales_volume, date) 
)    
INSERT INTO product_metadata
SELECT po."id", m."sales_volume", m."date"
FROM prod_order po
JOIN metadata m 
  ON po.rn = m.rn;

OUTPUT

enter image description here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

The following is a reasonable interpretation of what you want to do:

with i as (
      insert into products (title, description, price)
          values ('Dope product 1', 'Buy diz', 9.99),
                 ('Dope product 2', 'Buy diz', 8.99),
                 ('Dope product 3', 'Buy diz', 7.99)
          returning *
     ) 
insert into product_metadata (product_id, sales_volume, date)
    select i.product_id, v.sales_volume, v.date
    from (values ('Dope product 1', 80, '2017-03-21'),
                 ('Dope product 2', 50, '2017-03-21'), 
                 ('Dope product 3', 70, '2017-03-21')
         ) v(title, sales_volume, date) join
         i
         on i.title = v.title;

The basic answer is "use returning * and use a join to get the values". I needed to change the titles so they are unique.

Upvotes: 7

Related Questions