PostgreSQL array_agg(INTEGER[])

Question

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?

IIRC array_agg is used to concatenate grouped values into a single array, not to concatenate arrays. — Magisch
– Magisch, Commented Oct 20, 2017 at 12:41
array_agg agregates rows to array - eg select array_agg(oid) from pg_database — Vao Tsun
– Vao Tsun, Commented Oct 20, 2017 at 12:44
array_agg is attempting to do this array[array[1:18], array[1:24], array[1:48]] and that is not valid multi dimensional array. You need to write your own aggregate that will concat arrays instead. — Łukasz Kamiński
– Łukasz Kamiński, Commented Oct 20, 2017 at 12:48
After looking into this further I see what it is doing: array_agg({1,2,3},{4,5,6},{7,8,9}) => {{1,4,7},{2,5,8},{3,6,9}} is there an aggregate function that would concaternate so ?_agg({1},{2,3},{4,5,6}) => {1,2,3,4,5,6}? — Derek
– Derek, Commented Oct 20, 2017 at 12:49
and I don't feel I complete understand why it is not [3:90] in the end.. — Vao Tsun
– Vao Tsun, Commented Oct 20, 2017 at 12:49

Łukasz Kamiński · Accepted Answer · 2017-10-20 13:03:21Z

14

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)

answered Oct 20, 2017 at 13:03

Łukasz Kamiński

6,0301 gold badge26 silver badges33 bronze badges

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

3 Comments

Derek Over a year ago

Thank you. That is so simple that I wonder why this isn't a standard function.

Adam D. Over a year ago

If you're using v14+ you may need to tweak this. postgresql.org/message-id/3266664.1633045937%40sss.pgh.pa.us

Adam D. Over a year ago

It will work v14+ if you change it to: CREATE or replace AGGREGATE array_concat_agg(anycompatiblearray) ( SFUNC = array_cat, STYPE = anycompatiblearray ); Otherwise could nest array_cats: select array_cat(ARRAY[1,2,3], ARRAY[4,5])

Collectives™ on Stack Overflow

PostgreSQL array_agg(INTEGER[])

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related