25

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?

Improve this question

4 Answers 4

Reset to default
30

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Additional explanation based on your answer: Because 1 = ANY(ARRAY[1,2,3]) is legal, it is reasonable to also expect 1 = ANY(SELECT ARRAY[1,2,3]) to be legal, but it isn't because that'd create an ambiguity about whether you wanted 1 = ARRAY[1,2,3] or 1 = 1 OR 1 = 2 OR 1 = 3 and the parser isn't smart enough to figure out that only one can be legal. You can tell it that you really want the array-ANY form with 1 = ANY( (SELECT ARRAY[1,2,3])::int[] ) (which I would do instead of unnesting).
Ah, interesting. Thanks for the additional details.
@CraigRinger: thank you! Ken's answer helped me how to do it right, but your comment made me understand why my query was wrong.
1

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);
Improve this answer

1 Comment

But it seems slower .
1

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.

Improve this answer

Comments

0

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

Improve this answer

1 Comment

How is this different than the existing answer?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.