postgres array_agg ERROR: cannot accumulate arrays of different dimensionality

Question

I have a parcels table in postgresql in which the zoning and zoning_description columns are array_agg cast over to text. the new.universities table has 9 rows and I need to return 9 rows in the output.

The purpose of this query is to find all the properties these universities are located on and collapse there zoning types into 1 unique column and union/dissolve their geometries into multipolygons

select array_agg(distinct dp.zoning) zoning,array_agg(distinct dp.zoning_description) zoning_description,
    uni.school name_,uni.address,'University' type_,1000 buff,st_union(dp.geom) 
from new.universities uni join new.detroit_parcels_update dp
on st_intersects(st_buffer(uni.geom,-10),dp.geom)
group by name_,uni.address,type_,buff

I get this error

ERROR:  cannot accumulate arrays of different dimensionality
********** Error **********

ERROR: cannot accumulate arrays of different dimensionality
SQL state: 2202E

I can do array_agg(distinct dp.zoning::text) zoning etc.. but this returns a completely messed up column with nested arrays in arrays...

Based on the answer here is my updated query which does not work

select array_agg(distinct zoning_u) zoning,array_agg(distinct zoning_description_u) zoning_description,
        uni.school name_,uni.address,'University' type_,1000::int buff,st_union(dp.geom) geom
from new.detroit_parcels_update dp,unnest(zoning) zoning_u,
unnest(zoning_description) zoning_description_u
join new.universities uni
on st_intersects(st_buffer(uni.geom,-10),dp.geom)
group by name_,uni.address,type_,buff order by name_

get this error

ERROR:  invalid reference to FROM-clause entry for table "dp"
LINE 6: on st_intersects(st_buffer(uni.geom,-10),dp.geom)
                                                 ^
HINT:  There is an entry for table "dp", but it cannot be referenced from this part of the query.

********** Error **********

ERROR: invalid reference to FROM-clause entry for table "dp"
SQL state: 42P01
Hint: There is an entry for table "dp", but it cannot be referenced from this part of the query.
Character: 373

My Final query which worked was

with t as(select dp.zoning,dp.zoning_description,uni.school name_,uni.address,'University' type_,1000::int buff,st_union(dp.geom) geom
    from new.detroit_parcels_update dp
    join new.universities uni
    on st_intersects(st_buffer(uni.geom,-10),dp.geom)
    group by name_,uni.address,type_,buff,dp.zoning,zoning_description order by name_
    )
select name_,address,type_,buff,st_union(geom) geom,array_agg(distinct z) zoning, array_agg(distinct zd) zoning_description
from t,unnest(zoning) z,unnest(zoning_description) zd 
group by name_,address,type_,buff

klin · Accepted Answer · 2017-11-16T01:19:22.170

35

Example data:

create table my_table(name text, numbers text[], letters text[]);
insert into my_table values
    ('first',  '{1, 2}', '{a}'   ),
    ('first',  '{2, 3}', '{a, b}'),
    ('second', '{4}',    '{c, d}'),
    ('second', '{5, 6}', '{c}'   );

You should aggregate arrays elements, not arrays. Use unnest():

select 
    name, 
    array_agg(distinct number) as numbers, 
    array_agg(distinct letter) as letters
from 
    my_table, 
    unnest(numbers) as number, 
    unnest(letters) as letter
group by name;

  name  | numbers | letters 
--------+---------+---------
 first  | {1,2,3} | {a,b}
 second | {4,5,6} | {c,d}
(2 rows)

Alternatively, you can create a custom aggregate. You need a function to merge arrays (concatenation with duplicates removing):

create or replace function public.array_merge(arr1 anyarray, arr2 anyarray)
    returns anyarray language sql immutable
as $$
    select array_agg(distinct elem order by elem)
    from (
        select unnest(arr1) elem 
        union
        select unnest(arr2)
    ) s
$$;

create aggregate array_merge_agg(anyarray) (
    sfunc = array_merge,
    stype = anyarray
);

select 
    name, 
    array_merge_agg(numbers) as numbers, 
    array_merge_agg(letters) as letters
from my_table
group by name;

edited Nov 16 '17 at 01:19

answered Nov 16 '17 at 00:51

klin

112,967
15
204
232

sweet I shall give your first recommendation a try later tonight. Is unnest a standard postgres function? – ziggy Nov 16 '17 at 13:31
Yes, [see it in the documentation.](https://www.postgresql.org/docs/current/static/functions-array.html) – klin Nov 16 '17 at 13:36
2

i have never seen this syntax from `< my_table, unnest(numbers) as number, unnest(letters) as letter >` – ziggy Nov 16 '17 at 16:33
this makes more sense to me `< with t1 as(select name,unnest(numbers) numbers,unnest(letters) letters from my_table) select name, array_agg(distinct numbers) as numbers, array_agg(distinct letters) as letters from t1 group by name >` – ziggy Nov 16 '17 at 16:34
This is a lateral join. Set returning functions (like `unnest()`) basically should be called in the FROM clause. There is no reason to complicate the query adding CTE. The query in the answer is simpler and usually faster. See [table expressions in the documentation.](https://www.postgresql.org/docs/current/static/queries-table-expressions.html) – klin Nov 16 '17 at 17:08
Your query won't work well in Postgres 10, see [dbfiddle](http://dbfiddle.uk/?rdbms=postgres_10&fiddle=fa564ff2a17af4e74ad7268c8777cdae). – klin Nov 16 '17 at 19:19
having a bit of trouble formulating my query. Im editing my question – ziggy Nov 16 '17 at 23:48
First join `parcels` with `universities`, add lateral joins at the end. – klin Nov 17 '17 at 00:07
I ended up using a CTE :( but your answer led me to the correct answer – ziggy Nov 17 '17 at 00:08
1

You answer led me to my solution by utilizing unnest in the FROM clause. Thanks! – richddr Nov 20 '19 at 22:48
the FROM clause was also important for me. Works perfect in Postgres 13 – leole Sep 30 '21 at 07:51

score 4 · Answer 2 · answered May 03 '21 at 06:58

A much simpler alternative is to create a custom aggregate function (you only need to do this once)

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

Then replace array_agg for array_concat_agg:

SELECT
    array_concat_agg(DISTINCT dp.zoning) zoning,
    array_concat_agg(DISTINCT dp.zoning_description) zoning_description,
    uni.school name_,
    uni.address,
    'University' type_,
    1000 buff,
    st_union(dp.geom)
FROM
    new.universities uni
    JOIN new.detroit_parcels_update dp ON st_intersects(st_buffer(uni.geom, - 10), dp.geom)
GROUP BY
    name_,
    uni.address,
    type_,
    buff

postgres array_agg ERROR: cannot accumulate arrays of different dimensionality

2 Answers2

Linked