postgresql 运算符不存在：integer = integer[] in a query with ANY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25600598/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Operator does not exist: integer = integer[] in a query with ANY
提问by Pavel V.
I frequently used integer = ANY(integer[])syntax, but now ANY operator doesn't work. This is the first time I use it to compare a scalar with an integer returned from CTE, but I thought this shouldn't cause problems.
我经常使用integer = ANY(integer[])语法，但现在 ANY 运算符不起作用。这是我第一次使用它来比较标量和从 CTE 返回的整数，但我认为这不会引起问题。
My query:
我的查询：
WITH bar AS (
SELECT array_agg(b) AS bs
FROM foo
WHERE c < 3
)
SELECT a FROM foo WHERE b = ANY ( SELECT bs FROM bar);
When I run it, it throws following error:
当我运行它时，它会引发以下错误：
ERROR: operator does not exist: integer = integer[]: WITH bar AS ( SELECT array_agg(b) AS bs FROM foo WHERE c < 3 ) SELECT a FROM foo WHERE b = ANY ( SELECT bs FROM bar)
错误：运算符不存在：整数 = 整数[]：WITH bar AS (SELECT array_agg(b) AS bs FROM foo WHERE c < 3) SELECT a FROM foo WHERE b = ANY (SELECT bs FROM bar)
Details in this SQL Fiddle.
此SQL Fiddle 中的详细信息。
So what am I doing wrong?
那我做错了什么？
回答by khampson
Based on the error message portion operator does not exist: integer = integer[], it appears that the bscolumn needs to be unnested, in order to get the right hand side back to an integerso the comparison operator can be found:
根据错误消息部分operator does not exist: integer = integer[]，似乎bs需要unnest编辑该列，以便将右侧返回到 aninteger以便可以找到比较运算符：
WITH bar AS (
SELECT array_agg(b) AS bs
FROM foo
WHERE c < 3
)
SELECT a
FROM foo
WHERE b = ANY ( SELECT unnest(bs) FROM bar);
This results in the output:
这导致输出：
A
2
3
Given the doc for the ANY function:
鉴于ANY 函数的文档：
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the case where the subquery returns no rows).
右侧是一个带括号的子查询，它必须正好返回一列。使用给定的运算符评估左侧表达式并将其与子查询结果的每一行进行比较，这必须产生一个布尔结果。如果获得任何真结果，则 ANY 的结果为“真”。如果未找到真结果（包括子查询不返回任何行的情况），则结果为“假”。
... the error makes sense, as the left-hand expression is an integer-- column b-- while the right-hand expression is an array of integers, or integer[], and so the comparison ends up being of the form integer= integer[], which doesn't have an operator, and therefore results in the error.
...这个错误是有道理的，因为左边的表达式是一个integer-- 列b-- 而右边的表达式是一个integers 或 s的数组integer[]，所以比较最终的形式是integer= integer[]，它不t 有一个运算符，因此会导致错误。
unnesting the integer[]value makes the left- and right-hand expressions integers, and so the comparison can continue.
unnest对integer[]值进行赋值使左手和右手表达式为integers，因此可以继续进行比较。
Modified SQL Fiddle.
修改后的SQL 小提琴。
Note:that the same behavior is seen when using INinstead of = ANY.
注意：使用IN代替时会看到相同的行为= ANY。
回答by farhan
column needs to be unnest
列需要取消嵌套
WITH bar AS ( SELECT array_agg(b) AS bs FROM foo WHERE c < 3 ) SELECT a FROM foo WHERE b = ANY ( SELECT unnest(bs) FROM bar);
WITH bar AS ( SELECT array_agg(b) AS bs FROM foo WHERE c < 3 ) SELECT a FROM foo WHERE b = ANY ( SELECT unnest(bs) FROM bar);
回答by Rokuell Kent
without unnest
没有不安
WITH bar AS (
SELECT array_agg(b) AS bs
FROM foo
WHERE c < 3
)
SELECT a FROM foo WHERE ( SELECT b = ANY (bs) FROM bar);
回答by Kristian Sandstr?m
FYI, For me,
仅供参考，对我来说，
SELECT ... WHERE "id" IN (SELECT unnest(ids) FROM tablewithids)
SELECT ... WHERE "id" IN (SELECT unnest(ids) FROM tablewithids)
was incomparably faster than
比
SELECT ... WHERE "id" = ANY((SELECT ids FROM tablewithids)::INT[])
SELECT ... WHERE "id" = ANY((SELECT ids FROM tablewithids)::INT[])
Didn't do any research into why that was though.
没有对为什么会这样做任何研究。
