PostgreSQL array_agg(INTEGER[]) [英] PostgreSQL array_agg(INTEGER[])
问题描述
使用 Postgres 9.5,我想连接来自 GROUP BY 的整数数组.从文档看来,array_agg 应该能够做到这一点,但我得到:错误:无法累积不同维度的数组
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
在我的测试集上使用 array_dims 我得到 [1:18]、[1:24] 和 [1:48].我将其视为 3 个不同长度的一维数组.结果应该是一个维度为 [1:90] 的数组,我在这里遗漏了什么?
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?
推荐答案
继续评论中的讨论,我个人的建议是创建聚合.
Continuing from discussion in comments, my personal suggestion is to create aggregate.
CREATE AGGREGATE array_concat_agg(anyarray) (
SFUNC = array_cat,
STYPE = anyarray
);
然后你可以这样做:
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)
这篇关于PostgreSQL array_agg(INTEGER[])的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!