7

I can create an array of arrays:

select array[array[1, 2], array[3, 4]];
     array     
---------------
 {{1,2},{3,4}}

But I can't aggregated arrays:

select array_agg(array[c1, c2])
from (
    values (1, 2), (3, 4)
) s(c1, c2);
ERROR:  could not find array type for data type integer[]

What am I missing?

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Possible duplicate of http://stackoverflow.com/questions/6782268/array-agg-for-array-types – Akash Apr 11 '13 at 17:11

1 Answers1

15

I use:

CREATE AGGREGATE array_agg_mult(anyarray) (
    SFUNC = array_cat,
    STYPE = anyarray,
    INITCOND = '{}'
);

and queries like:

SELECT array_agg_mult( ARRAY[[x,x]] ) FROM generate_series(1,10) x;

Note that you must aggregate 2-dimensional arrays, so you'll often want to wrap an input array in a single-element ARRAY[array_to_aggregate] array constructor.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Here goes a question of efficiency. Looks like this wil have quadratic complexity. – Suor Jun 03 '17 at 15:08
  • @Suor `n(log n)` probably. For better results you'd use an unpacked array as an intermediate state, and a C function. – Craig Ringer Jun 06 '17 at 00:46