我想进行批量插入交易,但是我不太确定如何使用CTE或更有效的方法来进行交易。这是我到目前为止所拥有的:
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');CTE的问题是我不知道如何从第一次插入中获取单个ID语句插入到具有 product_id外键的第二条语句的相应记录中。
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.
推荐答案以下是合理的解释您想要做的事情:
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;基本答案是使用返回* 并使用 join 来获取值。我需要更改标题以便它们唯一。
The basic answer is "use returning * and use a join to get the values". I needed to change the titles so they are unique.
更多推荐
如何在Postgres中使用CTE使用外键插入多行?
发布评论