如何在Postgres中使用CTE使用外键插入多行? [英] How do I insert multiple rows with a foreign key using a CTE in Postgres?
问题描述
我想进行批量插入交易,但是我不太确定如何使用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使用外键插入多行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!