postgres array_agg错误:无法累积不同维数的数组 [英] postgres array_agg ERROR: cannot accumulate arrays of different dimensionality
问题描述
我在postgresql中有一个包裹表,其中zoning和zoning_description列被array_agg转换为文本. new.universities表具有9行,我需要在输出中返回9行.
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.
此查询的目的是查找这些大学所在的所有属性,并将这些区域的类型折叠为1个唯一的列,并将其几何合并/分解为多面体
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
我收到此错误
ERROR: cannot accumulate arrays of different dimensionality
********** Error **********
ERROR: cannot accumulate arrays of different dimensionality
SQL state: 2202E
我可以执行array_agg(distinct dp.zoning :: text)分区等.但是这会返回一个完全混乱的列,其中包含数组中的嵌套数组...
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_
遇到此错误
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
我最后的有效查询是
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
推荐答案
示例数据:
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}' );
您应该聚合数组元素,而不是数组.使用unnest()
:
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;
这篇关于postgres array_agg错误:无法累积不同维数的数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!