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

2013/3/13 Thor Michael Støre <thormichael(at)gmail(dot)com>:
> Hello,
>
> Could someone make sense of this for me?
>
> $ /Library/PostgreSQL/9.2/bin/psql -d postgres postgres
> psql (9.2.3)
> Type "help" for help.
>
> postgres=# select 1 = ANY (ARRAY[1,2,3]);
> ?column?
> ----------
> t
> (1 row)
>
> postgres=# select 1 = ANY (SELECT ARRAY[1,2,3]);
> ERROR: operator does not exist: integer = integer[]
> LINE 1: select 1 = ANY (SELECT ARRAY[1,2,3]);
> ^
> HINT: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> postgres=# select 1 = ANY ((SELECT ARRAY[1,2,3])::int[]);
> ?column?
> ----------
> t
> (1 row)
>
> Why do I have to add an explicit cast to int array on something that is an
> int array to begin with? Based on the error message containing "integer =
> integer[]" I'd say PostgreSQL manages to figure out the right type anyhow,
> and ::int[] shouldn't change anything, but I still get a message that
> doesn't make sense when I have an ANY there.

A bit tricky to explain...

select 1 = ANY (ARRAY[1,2,3])

-> "Is the integer value 1 contained in the specified array of integers?" (YES)

select 1 = ANY (SELECT ARRAY[1,2,3])

-> "Is the integer value 1 contained in the specified result set,
which happens to be an array (which is not comparable with an
integer)?" (NO)

select 1 = ANY ((SELECT ARRAY[1,2,3])::int[]);
-> "Is the value one contained in an array of integers which is
derived by converting a result set into an array?" (YES)

Note:

testdb=> SELECT array[1,2,3] = ANY (SELECT ARRAY[1,2,3]);
?column?
----------
t
(1 row)

I hope that makes some kind of sense...

Ian Barwick