CODEWITHSUNDEEP
sqlpostgresql
Derek
Derek

Reputation: 355

PostgreSQL array_agg(INTEGER[])

Using Postgres 9.5, I want to concaternate integer arrays from a GROUP BY. From the documentation is seems as though array_agg should be able to do this, but I get: ERROR: cannot accumulate arrays of different dimensionality

Using array_dims on my test set I get [1:18], [1:24] and [1:48]. I see this as 3 1-dimensional arrays of different lengths. The result should be a single array with dimension [1:90] What am I missing here?

Upvotes: 6

Views: 5746

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

Continuing from discussion in comments, my personal suggestion is to create aggregate.

CREATE AGGREGATE array_concat_agg(anyarray) (
  SFUNC = array_cat,
  STYPE = anyarray
);

Then you can do this:

SELECT column1
  FROM (VALUES (array[1,2,3]), (array[3,4]), (array[53,43,33,22])) arr;
    column1
---------------
 {1,2,3}
 {3,4}
 {53,43,33,22}
(3 rows)

SELECT array_concat_agg(column1)
  FROM (VALUES (array[1,2,3]), (array[3,4]), (array[53,43,33,22])) arr;
    array_concat_agg
-------------------------
 {1,2,3,3,4,53,43,33,22}
(1 row)

Upvotes: 13

Related Questions