Operator does not exist: integer = integer[] in a query with ANY

Question

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.

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)

Details in this SQL Fiddle.

So what am I doing wrong?

Answer 1

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

This results in the output:

A
2
3

Given the doc for the ANY function:

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

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

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

Modified SQL Fiddle.

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

Answer 2

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

Answer 3

FYI, For me,

SELECT ... WHERE "id" IN (SELECT unnest(ids) FROM tablewithids)

was incomparably faster than

SELECT ... WHERE "id" = ANY((SELECT ids FROM tablewithids)::INT[])

Didn't do any research into why that was though.