I need to aggregate rows with a single column of type INT[]
into a single array of type INT[]
.
The following results in an error "could not find array type for data type integer[]":
create temp table vals (j int[])
on commit drop;
insert into vals
values ('{1,2}'), ('{1,5}'), ('{3,6}');
select array_agg(j) from vals;
The result that I need from the example is a single row that looks like {1,2,1,5,3,6}
The order of records is unimportant as long as the paired numbers remain adjacent to each other in the final array.
I'm out of ideas. How can I get the result that I need?
(PostgreSQL 9.4, Ubuntu 15.04)
Best Answer
Order of source rows irrelevant, source is array column
Your updated requirements are much simpler for two reasons:
Simple table:
Simple query:
Order of source rows relevant, source are integer columns
Not as simple. Based on this table:
There are various ways to preserve order. I think this should be among the fastest - with an ARRAY constructor:
My first draft had
ORDER BY i, j
after theLATERAL
join, which would not guarantee the order of columns. I pulled theORDER BY
into a subselect to produce the correct order now. This works, but it's not documented. To be absolutely sure, you have to add an explicitORDER BY
later:About guaranteed order:
About the
VALUES
expression in aLATERAL
join:Another way with - this time with
array_agg()
:The ARRAY constructor is faster,
array_agg()
is easier to integrate with multiple result columns.SQL Fiddle.
The query in your test case would actually work in Postgres 9.5 (currently beta, to be released in the course of 2015). Quoting the release notes for pg 9.5:
However, you get a 2-dimenstional array instead, not your desired result. You can easily implement the same (but slower) aggregate function in older versions: