如何使用VARCHAR ARRAY_AGG（）[] [英] How to use array_agg() for varchar[]

问题描述

我对我们的名为 min_crew 数据库具有不同的字符数组，如'{CA，FO，FA}'一栏。

我在那里我试图让没有成功这些阵列聚集的查询：

  AS seids SELECT use.user_sched_id，ARRAY_AGG（se.sched_entry_id）
     ，ARRAY_AGG（se.min_crew）
从本质上base.sched_entry
   LEFT JOIN base.user_sched_entry使用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 ，但我不断收到此错误：

 错误：找不到数组类型的数据类型字符改变[]
 

如果我删除code的 ARRAY_AGG（se.min_crew）部分，我得到一个表格与 user_sched_id =返回2131 和 seids ='{623，625} 。

解决方案

标准聚合函数的 ARRAY_AGG（） 仅适用于基本类型，而不是数组类型作为输入。
（但的Postgres 9.5 + 有一个<一个href=\"http://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE\"相对=nofollow>的新变种 ARRAY_AGG（） 的就可以了！）

您可以使用自定义聚合函数的 array_agg_mult（） 在这个相关答案定义：结果
<一href=\"http://stackoverflow.com/questions/11762398/selecting-data-into-a-postgres-array-format/11763245#11763245\">Selecting数据转换成一个Postgres阵列

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

  AS seids SELECT use.user_sched_id，ARRAY_AGG（se.sched_entry_id）
      ，array_agg_mult（ARRAY [se.min_crew]）作为min_crew_arr
从本质上base.sched_entry
LEFT JOIN base.user_sched_entry使用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（） 来聚集文本 - preferably用不同的分隔符。在我的例子使用新行：

  AS seids SELECT use.user_sched_id，ARRAY_AGG（se.sched_entry_id）
      ，string_agg（array_to_string（se.min_crew，'，'），E'\\ n'）AS min_crews
FROM ...
 

正常化

您可能要考虑正火广告模式开始。通常情况下，你将执行这样一个n：用一个单独的表就像这个例子概括米的关系：结果
<一href=\"http://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql/9790225#9790225\">How实施PostgreSQL中的许多一对多的关系？

I have a column in our database called min_crew that has varying character arrays such as '{CA, FO, FA}'.

I have a query where I'm trying to get aggregates of these arrays without success:

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;

Both 623 and 625 have the same use.user_sched_id, so the result should be the grouping of the seids and the min_crew, but I just keep getting this error:

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

If I remove the array_agg(se.min_crew) portion of the code, I do get a table returned with the user_sched_id = 2131 and seids = '{623, 625}'.

解决方案

The standard aggregate function array_agg() only works for base types, not array types as input. (But Postgres 9.5+ has a new variant of array_agg() that can!)

You could use the custom aggregate function array_agg_mult() as defined in this related answer:
Selecting data into a Postgres array

Create it once per database. Then your query could work like this:

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;

There is a detailed rationale in the linked answer.

Extents have to match

In response to your comment, consider this quote from the manual on array types:

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

There is no way around that, the array type does not allow such a mismatch in Postgres. You could pad your arrays with NULL values so that all dimensions have matching extents.

But I would rather translate the arrays to a comma-separated lists with array_to_string() for the purpose of this query and use string_agg() to aggregate the text - preferably with a different separator. Using a newline in my example:

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   ...

Normalize

You might want to consider normalizing your schema to begin with. Typically, you would implement such an n:m relationship with a separate table like outlined in this example:
How to implement a many-to-many relationship in PostgreSQL?

这篇关于如何使用VARCHAR ARRAY_AGG（）[]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！