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)

  • What defines the order of rows in vals? You must define it, there is no natural order. Maybe just ORDER BY i, j? Also, please clarify: you mention rows with a single column of type INT[], but demonstrate a table with two plain int columns. Which is it? – Erwin Brandstetter Oct 9 '15 at 14:04
  • @ErwinBrandstetter The temp table is just for illustration purposes. In reality, my input table is a single column of type INT[]. The example accomplishes this by wrapping i and j in an ARRAY call. I've updated the question to make this more clear. – spencerrecneps Oct 9 '15 at 21:35
  • The updated question is a simpler variant. Consider the added solution. – Erwin Brandstetter Oct 10 '15 at 1:09
up vote 2 down vote accepted

Order of source rows irrelevant, source is array column

Your updated requirements are much simpler for two reasons:

  • We don't need to establish any order among source rows.
  • And the array already establishes order among array elements.

Simple table:

CREATE TABLE vals (j int[]);

Simple query:

SELECT ARRAY(SELECT elem FROM vals, unnest(j) elem) AS arr;

Order of source rows relevant, source are integer columns

Not as simple. Based on this table:

CREATE TABLE vals (i int, j int);

There are various ways to preserve order. I think this should be among the fastest - with an ARRAY constructor:

SELECT ARRAY(
   SELECT t.elem
   FROM  (SELECT i, j FROM vals ORDER BY i, j) v
        , LATERAL (VALUES (v.i), (v.j)) t(elem)
   ) AS arr;

My first draft had ORDER BY i, j after the LATERAL join, which would not guarantee the order of columns. I pulled the ORDER 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 explicit ORDER BY later:

SELECT ARRAY (
   SELECT elem
   FROM   vals v, LATERAL (VALUES (1, v.i), (2, v.j)) t(ordr, elem)
   ORDER  BY v.i, v.j, ordr
   ) AS arr;

About guaranteed order:

About the VALUES expression in a LATERAL join:

Another way with - this time with array_agg():

SELECT array_agg(elem) AS arr
FROM  (
   SELECT i, j, 1 AS ordr, i AS elem FROM vals
   UNION ALL
   SELECT i, j, 2 AS ordr, j AS elem FROM vals
   ORDER BY i, j, ordr
   ) sub;

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:

Allow array_agg() and ARRAY() to take arrays as inputs (Ali Akbar, Tom Lane)

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:

You can define your own aggregation function:

CREATE AGGREGATE array_agg_flat (anyarray)
(
    sfunc = array_cat,
    stype = anyarray,
    initcond = '{}'
);

So now it will do exactly what you want:

select array_agg_flat(j) from vals;

RESULT:
{1,2,1,5,3,6}

In PostgreSQL 9.5+, one could use select string_to_array(string_agg(array_to_string(array_column_here, ','), ','), ',') from xxx to aggregate arrays into 1-dimension array

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.