PostgreSQL row inserting CTE with extra returning fields

Question

Long time reader first time asker here!

In my database, I have the following (simplified) sample tables with some example data,

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;

What I'd like to do is insert a row in zip_collections for file_zip.zip grouped on file_id and then create a new table, file_collections, populated with the file_id and the zip_collection_id of the rows that were inserted. It seemed like a good use of a Common Table Expression, but I haven't been able to get it to work quite right.

Here's what I tried to write,

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;

The query fails to execute, I can't have file_id in the returning clause of the insert because it isn't part of zip_collections. Is there a way to compose this query without temporarily adding a file_id column to zip_collections?

Here is a link to a SQL Fiddle Sample

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

Thank you for your time!

Edit: I expanded on the sample data. Here's some example of the output I'm expecting.

file_collections

|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

The documentation states that the columns returned by the insert need to be actually inserted columns:

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

You can always join it back in some way like below and get the file_id that way:

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
;