title: PostgreSQL row inserting CTE with extra returning fields
body: <p>Long time reader first time asker here!</p>
  
  <p>In my database, I have the following (simplified) sample tables with some example data,</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>What I'd like to do is insert a row in <strong>zip_collections</strong> for <strong>file_zip</strong>.<em>zip</em> grouped on <em>file_id</em> and then create a new table, <strong>file_collections</strong>, populated with the <em>file_id</em> and the <em>zip_collection_id</em> 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.</p>
  
  <p>Here's what I tried to write,</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>The query fails to execute, I can't have <em>file_id</em> in the returning clause of the insert because it isn't part of <strong>zip_collections</strong>. Is there a way to compose this query without temporarily adding a <em>file_id</em> column to <strong>zip_collections</strong>?</p>
  
  <p>Here is a link to a SQL Fiddle Sample</p>
  
  <p><a href="http://sqlfiddle.com/#!15/e72f3/6" rel="nofollow">http://sqlfiddle.com/#!15/e72f3/6</a></p>
  
  <p>Thank you for your time!</p>
  
  <p><em>Edit: I expanded on the sample data. Here's some example of the output I'm expecting.</em></p>
  
  <p>file_collections</p>
  
  <pre><code>|zip_collection_id|file_id|
  |1|1|
  |2|2|
  |3|3|
  |4|4|
  </code></pre>
  
  <p>zip_collections</p>
  
  <pre><code>|zip_collection_id|zips|
  |1|66211,66212|
  |2|66101|
  |3|66600,66601,66602|
  |4|66600|
  </code></pre>