运算符不存在:integer = integer[] in a query with ANY [英] Operator does not exist: integer = integer[] in a query with ANY

问题描述

我经常使用 integer = ANY(integer[]) 语法，但现在 ANY 运算符不起作用.这是我第一次使用它来比较标量和从 CTE 返回的整数，但我认为这不会引起问题.

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.

我的查询:

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:

错误:运算符不存在:整数 = 整数[]:WITH bar AS (SELECT array_agg(b) AS bs FROM foo WHERE c <3 ) 从 foo 中选择一个WHERE b = ANY ( SELECT bs FROM bar)

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)

此SQL Fiddle中的详细信息.

那我做错了什么?

推荐答案

根据错误信息部分operator does not exist: integer = integer[]，看来bs 列需要 unnest ed，以便将右侧恢复为 integer，以便可以找到比较运算符:

Based on the error message portion operator does not exist: integer = integer[], it appears that the bs column needs to be unnested, in order to get the right hand side back to an integer so the comparison operator can be found:

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);

结果如下:

A
2
3

鉴于任何函数的文档:

右边是一个带括号的子查询，它必须返回正好一列.评估和比较左边的表达式使用给定的运算符到子查询结果的每一行，其中必须产生一个布尔结果.ANY 的结果是true"，如果有的话结果得到.如果未找到真结果，则结果为假"(包括子查询不返回任何行的情况).

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

... 这个错误是有道理的，因为左边的表达式是一个 integer -- 列 b -- 而右边的表达式是一个数组integers 或 integer[]，所以比较结果是 integer = integer[]>，它没有运算符，因此会导致错误.

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

unnestinteger[] 值使左手和右手表达式 integer s，因此比较可以继续.

unnesting the integer[] value makes the left- and right-hand expressions integers, and so the comparison can continue.

修改了SQL Fiddle.

注意:当使用 IN 而不是 = ANY 时会看到相同的行为.

Note: that the same behavior is seen when using IN instead of = ANY.

这篇关于运算符不存在:integer = integer[] in a query with ANY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！