如何在Postgresql中整合整数？ [英] How to aggragate integers in postgresql?

问题描述

我有一个查询，提供ID列表：

I have a query that gives list of IDs:

ID
2
3
4
5
6
25

ID 是整数。

我想得到这样的结果在整数的 ARRAY 类型中：

I want to get that result like that in ARRAY of integers type:

ID
2,3,4,5,6,25

我写了这个查询：

select string_agg(ID::text,',')
from  A
where .....

我必须将其转换为文本，否则它将无法正常工作。 string_agg 期望得到（text，text）

I have to convert it to text otherwise it won't work. string_agg expect to get (text,text)

这样做的好处是，此结果以后应在期望 ARRAY 整数的地方使用。

this works fine the thing is that this result should later be used in many places that expect ARRAY of integers.

我试过了：

select ('{' || string_agg(ID::text,',') || '}')::integer[]
from  A
WHERE ...

给出：类型为 int4整数[] $ b $的 {2,3,4,5,6,25} b，但这不是正确的类型...我需要与 ARRAY 相同的类型。

which gives: {2,3,4,5,6,25} in type int4 integer[] but this isn't the correct type... I need the same type as ARRAY.

SELECT ARRAY [4,5] 给出 array integer []

用简单的话来说，我希望查询的结果可以使用（例如）：

in simple words I want the result of my query to work with (for example):

select *
from b
where b.ID = ANY (FIRST QUERY RESULT)   // aka: = ANY (ARRAY[2,3,4,5,6,25])

由于任何期望的数组而失败，并且它不适用于常规整数[]，我得到一个错误：

this is failing as ANY expect array and it doesn't work with regular integer[], i get an error:

错误：运算符不存在：integer = integer []

ERROR: operator does not exist: integer = integer[]

注意：查询结果是函数的一部分，将保存在变量中以供以后工作。请不要将其带到绕过问题的地方，并提供不会给出整数 ARRAY 的解决方案c>。

note: the result of the query is part of a function and will be saved in a variable for later work. Please don't take it to places where you bypass the problem and offer a solution which won't give the ARRAY of Integers.

编辑：为什么

select *
from b
where b.ID = ANY (array [4,5]) 

正在工作。但是

select *
from b
where b.ID = ANY(select array_agg(ID) from A where ..... )

不起作用

select *
from b
where b.ID = ANY(select array_agg(4))

也不起作用

错误仍然是：

错误：运算符不存在：integer = integer []

ERROR: operator does not exist: integer = integer[]

推荐答案

表达式 select array_agg（4）返回行集（实际上是具有1行的行集）。因此查询

Expression select array_agg(4) returns set of rows (actually set of rows with 1 row). Hence the query

select *
from b
where b.id = any (select array_agg(4))  -- ERROR

尝试将整数（b.id）与行值进行比较（具有1个类型为integer []的列）。

tries to compare an integer (b.id) to a value of a row (which has 1 column of type integer[]). It raises an error.

要解决此问题，您应该使用返回整数（而不是整数数组）的子查询：

To fix it you should use a subquery which returns integers (not arrays of integers):

select *
from b
where b.id = any (select unnest(array_agg(4)))

或者，您可以将 select array_agg（4）的结果的列名放在任何的参数，例如：

Alternatively, you can place the column name of the result of select array_agg(4) as an argument of any, e.g.:

select *
from b
cross join (select array_agg(4)) agg(arr)
where b.id = any (arr)

或

with agg as (
    select array_agg(4) as arr)
select *
    from b
    cross join agg
    where b.id = any (arr)

更正式地说，前两个查询使用 ANY 形式：

More formally, the first two queries use ANY of the form:

expression operator ANY (subquery)

和其他两种使用

expression operator ANY (array expression)

就像文档中所述： 9.22.4。 ANY / SOME
和 9.23.3 。任意/某些（数组）。

这篇关于如何在Postgresql中整合整数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！