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|

I forgot the group by statement in the sample, thanks to Martin Strejc for noticing! I expanded the sample data and added samples of what the expected output is. — Jeremy Liberman
– Jeremy Liberman, Commented Aug 10, 2014 at 4:24

ced-b · Accepted Answer · 2014-08-09 17:01:16Z

3

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
;

answered Aug 9, 2014 at 17:01

ced-b

4,0371 gold badge31 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy Liberman Over a year ago

Thanks for your response. I'm sorry but I had a mistake in the example query when you wrote this and have since clarified it.

Martin Strejc · Accepted Answer · 2014-08-09 16:44:13Z

0

I think your case doesn't make a sense without grouping zip by file id. So just add grouping and the file_id into the table 'zip_collections' should be enough.

Structure and init

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

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

SQL:

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

select * from file_collections;

answered Aug 9, 2014 at 16:44

Martin Strejc

4,3872 gold badges26 silver badges39 bronze badges

1 Comment

Jeremy Liberman Over a year ago

While there's a fair chance this is exactly what I'm going to end up doing, my question was whether there was a way to write this query without adding file_id to zip_collections.

Collectives™ on Stack Overflow

PostgreSQL row inserting CTE with extra returning fields

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related