6

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?

Derek
  • 345
  • 3
  • 15
  • IIRC array_agg is used to concatenate grouped values into a single array, not to concatenate arrays. – Magisch Oct 20 '17 at 12:41
  • array_agg agregates rows to array - eg `select array_agg(oid) from pg_database` – Vao Tsun Oct 20 '17 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 Oct 20 '17 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 Oct 20 '17 at 12:49
  • and I don't feel I complete understand why it is not [3:90] in the end.. – Vao Tsun Oct 20 '17 at 12:49
  • for the above - array_agg unnested multidimentional array will concat as you want – Vao Tsun Oct 20 '17 at 12:51
  • How would you call this? I have tried `array_agg(unnest("array_col"))` and get `ERROR: set-valued function called in context that cannot accept a set` – Derek Oct 20 '17 at 12:55
  • @Derek You would have to use subselect on query that would unnest and produce row set, then array agg on that subselect. Or upgrade to pg10 – Łukasz Kamiński Oct 20 '17 at 12:59
  • Thank you. What does pg10 offer, I can't see anything in the documentation. – Derek Oct 20 '17 at 13:03
  • nvm, Just tested it on pg10 and it is actually still not possible without subselect :( – Łukasz Kamiński Oct 20 '17 at 13:40

1 Answers1

11

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)
Łukasz Kamiński
  • 5,630
  • 1
  • 19
  • 32
  • Thank you. That is so simple that I wonder why this isn't a standard function. – Derek Oct 20 '17 at 13:08
  • If you're using v14+ you may need to tweak this. https://www.postgresql.org/message-id/3266664.1633045937%40sss.pgh.pa.us – Adam D. Mar 14 '22 at 22:25
  • 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])` – Adam D. Mar 14 '22 at 22:39