sql - 如何将 array_agg() 用于 varchar[]

Question

我在我们的数据库中有一个名为 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，所以结果应该是 seid 和 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}'.

Answer 1

标准聚合函数 array_agg()仅适用于基本类型，不适用于数组类型作为输入。(但是 Postgres 9.5+ 有一个 new variant of array_agg() 可以!)

您可以使用此相关答案中定义的自定义聚合函数 array_agg_mult():
Selecting data into a Postgres array

每个数据库创建一次。那么您的查询可以像这样工作:

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;

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

范围必须匹配

作为对您评论的回应，请引用 the manual on array types 中的这句话:

Multidimensional arrays must have matching extents for each dimension. A mismatch causes an error.

没有办法解决这个问题，数组类型在 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   ...

规范化

您可能需要考虑 normalizing您的模式开始。通常，您将使用单独的表实现这样的 n:m 关系，如本示例中所述:
How to implement a many-to-many relationship in PostgreSQL?