Russell Smith <mr-r...@pws.com.au> writes:
> Is anybody able to explain the following behaviour?
> mr-russ=# SELECT 'BU'::varchar = ANY ((select '{BU,CI}'::varchar[]));
> ERROR: operator does not exist: character varying = character varying[]
> LINE 1: SELECT 'BU'::varchar = ANY ((select '{BU,CI}'::varchar[]));
> ^
> HINT: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> mr-russ=# SELECT 'BU'::varchar = ANY ((select
> '{BU,CI}'::varchar[])::varchar[]);
> ?column?
> ----------
> t
> (1 row)
For ANY (or ALL) with a sub-select, the sub-select is expected to return
rows, and the left-hand value is compared to each row's contained value.
This is required behavior per SQL standard.
If the right-hand argument of ANY/ALL is *not* a sub-select, then it's
expected to be an expression yielding an array value, and the left-hand
value is compared to each array element. AFAIR, this is not in the SQL
standard but is a Postgres extension.
In your second example, the RHS is a cast expression, not directly a
sub-select, so it behaves as per the second rule. The sub-select
embedded within it doesn't count.
There isn't any provision for ANY/ALL with a sub-select returning a
series of array values; that would require iteration in "two
dimensions", and we don't do that. It would be contrary to spec in any
case, I think, and would break existing use cases where the ANY/ALL
operator is one that takes a scalar on the left and an array on the
right.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
