PostgreSQL 行插入带有额外返回字段的 CTE

Question

老读者第一次在这里提问!

在我的数据库中，我有以下(简化的)样本表和一些示例数据，

create table file_zips (file_id int, zip varchar(5), total int);
create table zip_collections (id serial primary key, zips varchar(5)[]);

insert into file_zips select 1, 66211, 4;
insert into file_zips select 1, 66212, 17;

insert into file_zips select 2, 66101, 1;

insert into file_zips select 3, 66600, 2;
insert into file_zips select 3, 66601, 2;
insert into file_zips select 3, 66602, 2;

insert into file_zips select 4, 66600, 1;

我想做的是在 zip_collections 中为 file_zip 插入一行。zip 按 file_id 分组> 然后创建一个新表 file_collections，其中填充了插入行的 file_id 和 zip_collection_id。这似乎是对公用表表达式的一种很好的使用，但我一直无法让它正常工作。

这是我试着写的，

with w as (
  insert into zip_collections (zips) 
  select array_agg(zip)
  from file_zips
  group by file_id
  returning id, file_id
)
select id as zip_collection_id, file_id
into file_collections
from w;

查询无法执行，我不能在插入的返回子句中包含 file_id，因为它不是 zip_collections 的一部分。有没有一种方法可以在不临时向 zip_collections 添加 file_id 列的情况下编写此查询？

这是 SQL Fiddle 示例的链接

http://sqlfiddle.com/#!15/e72f3/6

感谢您的宝贵时间!

编辑:我扩展了示例数据。这是我期望的一些输出示例。

文件集合

|zip_collection_id|file_id|
|1|1|
|2|2|
|3|3|
|4|4|

zip_collections

|zip_collection_id|zips|
|1|66211,66212|
|2|66101|
|3|66600,66601,66602|
|4|66600|

Answer 1

documentation指出插入返回的列需要实际插入列:

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted.

您总是可以像下面这样以某种方式加入它并以这种方式获取 file_id:

with w as (
   insert into zip_collections (zips) 
   select array_agg(zip)
   from file_zips
   returning id, zips
)
select w.id as zip_collection_id, z.file_id
into file_collections
from w
left join file_zips z on w.zips[1] = z.zip
;