如何在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屋！