Re: SELECT 1 = ANY (SELECT ARRAY[1, 2, 3]) -> ERROR: operator does not exist: integer = integer[] ?

Ian Lawrence Barwick <barwick(at)gmail(dot)com> writes:
> 2013/3/13 Thor Michael Stre <thormichael(at)gmail(dot)com>:
>> Could someone make sense of this for me?

>> postgres=# select 1 = ANY (SELECT ARRAY[1,2,3]);
>> ERROR: operator does not exist: integer = integer[]

> A bit tricky to explain...

Yeah. The short answer is that "foo = ANY (SELECT ...)" is mandated by
standard to mean "compare foo to each value in the rowset returned by
the sub-SELECT". So the above is invalid, and the error message is
giving a perfectly clear reason why.

Postgres has abused the ANY notation by saying that, if the right-hand
side is not a SELECT at the top level (which would be invalid on its
face per spec), then it must be an expression delivering an array value,
and we compare foo to each array element. This isn't terribly
consistent but it's about the only easy way to shoehorn such an
operation into the language at all.

regards, tom lane