6

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.

Improve this question
7
  • Edit your question and show the rows that you want inserted into the second table. Commented Mar 22, 2017 at 1:14
  • @GordonLinoff These are the rows I want insert into the product_metadata table: (ID FOR 'Dope product 1', 80, '2017-03-21'), (ID FOR 'Dope product 2', 50, '2017-03-21'), (ID FOR 'Dope product 3', 70, '2017-03-21'). Would your proposed answer work if you do the join on the title (i.title = v.title)? You're not referencing the id from the first insert so I'm not sure if it'd work Commented Mar 22, 2017 at 1:27
  • Jamaal: @Gordon is doing the JOIN on the title (i.title = v.title) the problem is you cant have two of the same items on the first insert. I did a solution for solve that but is really messy. My suggestion insert product and metadata one by one. Commented Mar 22, 2017 at 1:59
  • @JuanCarlosOropeza The problem with the one by one insert is it's too slow if you have a lot of data! Commented Mar 22, 2017 at 2:03
  • I dont know why you mean slow. I can insert 30k records in 5 seconds. Add some paralelism to your app. Commented Mar 22, 2017 at 2:07

2 Answers 2

Reset to default
8

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

@TimBiegeleisen My guess is there is product_id autonumeric in products that is why he is returning id
@TimBiegeleisen . . . I always call that CTE i. I missed that I left in the OP's original name.
Really helped me in my case. switched the nested value statement for a with statement using the previously returned inserted ids. Very nice!
0

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

Improve this answer

2 Comments

Your solution looks like it works but I don't think it'd handle the scenario where ids of the products table and row_number() are out of sync. For example if you delete a few rows in the products table and the ids now have this sequence: 1, 2, 3, 6, 8, 9, it doesn't seem like joining on row_number would work in this case, or maybe I'm wrong
Yes, that handle, first the new id from insert are always in sequence. But even that ... you dont use that value to match... you use the rn you get from row_number() to do the match. And that go from 1..n, the problem is you need to add the insert order for the metadata some how. Maybe if your date have seconds you could use it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.