如何为product表中每个info_id为空的行创建独立info关联行？

环境搭建 #

create table info(
  info_id serial primary key, 
  info_text text
);
create table product(
  prod_id serial primary key, 
  info_id int /*unique*/, 
  description text unique, 
  price int);

insert into product (description, price) values ('pen', 1), ('notebook', 2), ('ruller', 3);

当前问题 #

尝试更新product表中所有info_id为null的行，期望为每个product行创建独立的info记录并关联，但当前SQL仅创建1条info记录，所有product行都关联到该记录：

with new_info as (insert into info default values returning info_id)
update product
set info_id = (select info_id from new_info)
where product.info_id is null;

执行后查询结果：

select * from product, info where product.info_id = info.info_id;

prod_id | info_id | description | price | info_id | info_text 
---------+---------+-------------+-------+---------+-----------
       1 |       1 | pen         |     1 |       1 | [null]
       2 |       1 | notebook    |     2 |       1 | [null]
       3 |       1 | ruller      |     3 |       1 | [null]

解决方案 #

要为每个需要更新的product行创建独立的info记录，需要让每个product行触发一次info表的插入操作。可以借助PostgreSQL的隐式LATERAL关联实现：

UPDATE product p
SET info_id = ni.info_id
FROM (
    SELECT prod_id, (INSERT INTO info DEFAULT VALUES RETURNING info_id) AS info_id
    FROM product
    WHERE info_id IS NULL
) ni
WHERE p.prod_id = ni.prod_id;

验证结果 #

执行上述更新后，再次查询关联数据：

select * from product join info on product.info_id = info.info_id;

会得到每个product对应独立的info记录：

prod_id | info_id | description | price | info_id | info_text 
---------+---------+-------------+-------+---------+-----------
       1 |       1 | pen         |     1 |       1 | [null]
       2 |       2 | notebook    |     2 |       2 | [null]
       3 |       3 | ruller      |     3 |       3 | [null]

原SQL问题分析 #

原CTE中的INSERT语句仅执行一次，返回单一info_id，因此所有符合条件的product行都会被设置为这个相同的ID。而通过子查询为每个product行单独执行INSERT，就能生成独立的info记录并完成正确关联。

内容的提问来源于stack exchange，提问作者Alex V