如何将array_agg（）用于varchar []

一尘不染

如何将array_agg（）用于varchar []

sql

我的数据库中有一列，该列min_crew具有各种字符数组，例如'{CA, FO, FA}'。

我有一个查询，我试图获得这些数组的聚合而没有成功：

SELECT use.user_sched_id, array_agg(se.sched_entry_id) AS seids
     , array_agg(se.min_crew) 
FROM base.sched_entry se
   LEFT JOIN base.user_sched_entry use ON se.sched_entry_id = use.sched_entry_id
WHERE se.sched_entry_id = ANY(ARRAY[623, 625])
GROUP BY user_sched_id;

623和625具有相同的值use.user_sched_id，因此结果应该是seids和的分组min_crew，但我一直不断遇到此错误：

ERROR:  could not find array type for data type character varying[]

如果删除array_agg(se.min_crew)部分代码，则确实会获得带有user_sched_id = 2131和返回的表seids ='{623, 625}'。

阅读 210

2021-03-10

共1个答案

一尘不染

标准聚合函数array_agg()仅适用于基本类型，不适用于输入的数组类型。（但是 Postgres 9.5+
具有该功能的新变体array_agg()！）

您可以使用 array_agg_mult() 此相关答案中定义的自定义聚合函数：将
数据选择到Postgres数组中

每个数据库创建一次。然后您的查询可以像这样工作：

SELECT use.user_sched_id, array_agg(se.sched_entry_id) AS seids
      ,array_agg_mult(ARRAY[se.min_crew]) AS min_crew_arr
FROM   base.sched_entry se
LEFT   JOIN base.user_sched_entry use USING (sched_entry_id)
WHERE  se.sched_entry_id = ANY(ARRAY[623, 625])
GROUP  BY user_sched_id;

链接的答案中有详细的理由。

范围必须匹配

为了回应您的评论，请考虑有关数组类型的手册中的以下引号：

多维数组必须具有每个维的匹配范围。不匹配会导致错误。

没有办法解决，数组类型不允许在Postgres中出现这种不匹配的情况。您可以使用NULL值填充数组，以便所有维都具有匹配范围。

但是我宁愿array_to_string()出于查询的目的将数组转换为逗号分隔的列表，并使用-
最好使用不同的分隔符string_agg()进行聚合text。在我的示例中使用换行符：

SELECT use.user_sched_id, array_agg(se.sched_entry_id) AS seids
      ,string_agg(array_to_string(se.min_crew, ','), E'\n') AS min_crews
FROM   ...

归一化

您可能要考虑从一开始就规范化架构。

2021-03-10