PostgreSQL Question posted in
The official documentation can be found here.

Postgresql ERROR: cannot accumulate arrays of different dimensionality

I have a postgres query I’m trying to aggregate an array of arrays with different dimensions, a simplified form of the query can be written as:

SELECT n, ARRAY(SELECT UNNEST(a) FROM (SELECT ARRAY_AGG(a) AS a) a) AS a, SUM(q) AS q
FROM (
    SELECT 'foo' AS n, '{1,2}'::integer[] AS a, 1 AS q
    UNION ALL
    SELECT 'foo' AS n, '{3,4,5}'::integer[] AS a, 1 AS q
) results
GROUP BY n

This results in an error:

Query 1 ERROR at Line 1: : ERROR: cannot accumulate arrays of different dimensionality

The result i’m hoping for is:

n a q
‘foo’ {1,2,3,4,5} 2

However if the arrays have the same dimensions it works fine, eg:

SELECT n, ARRAY(SELECT UNNEST(a) FROM (SELECT ARRAY_AGG(a) AS a) a) AS a, SUM(q) AS q
FROM (
    SELECT 'foo' AS n, '{1,2,3}'::integer[] AS a, 1 AS q
    UNION ALL
    SELECT 'foo' AS n, '{4,5,6}'::integer[] AS a, 1 AS q
) results
GROUP BY n

Output:

n a q
‘foo’ {1,2,3,4,5,6} 2

I have tried asking every LLM on the internet and none can answer it, so it’s down to you humans now, please help.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I answered my own question using built-in JSON functions.

    SELECT n, ARRAY(SELECT JSONB_ARRAY_ELEMENTS(JSONB_ARRAY_ELEMENTS(JSONB_AGG(a)))) AS a, SUM(q) AS q
FROM (
    SELECT 'foo' AS n, '{1,2}'::integer[] AS a, 1 AS q
    UNION ALL
    SELECT 'foo' AS n, '{3,4,5}'::integer[] AS a, 1 AS q
) results
GROUP BY n

  2. 0

    Create once per database:

    CREATE AGGREGATE array_concat (anycompatiblearray) (
  sfunc = array_cat
, stype = anycompatiblearray
, initcond = '{}'
);

    Then:

    SELECT n, array_concat(a) AS a, sum(q) AS q
FROM  (
    SELECT 'foo' AS n, '{1,2}'::int[] AS a, 1 AS q
    UNION ALL
    SELECT 'foo' AS n, '{3,4,5}'::int[] AS a, 1 AS q
) results
GROUP BY n;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search