Sql 为表中每个缺少的引用创建新行

简而言之，我正在努力做到这一点:

设置:

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);

现在，我try 更新info_id引用为null的所有product行，方法是在info表中创建一个新行，然后将对它的引用分配给产品:

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;

The problem is that it only creates one info and then assigns all products to it. I expect it to create one companion info row for each product.个

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]