BUG #6690: ALL and ANY array operators
The following bug has been logged on the website:
Bug reference: 6690
Logged by: sergey
Email address: sergey-1987@yandex.ru
PostgreSQL version: 9.1.2
Operating system: FreeBSD 8.1
Description:
As I see in documentation,
http://www.postgresql.org/docs/current/static/functions-aggregate.html there
is an ambiguity with bool_or aggregate and ANY array operator, so bool_or
cannot has standard name ANY. So ANY should always mean array operator. But
such query produces syntax error:
select 1 = ANY((select ARRAY[1, 2]::integer[]))
I.e. when I try to check is some element in array, that is calculated in
subquery, I cannot. I can use:
select ARRAY[1] && (select ARRAY[1, 2]::integer[])
but what about ANY operator?
For example this works:
select 1 = ANY(ARRAY[1, 2]::integer[]);
select 1 in (select * from unnest(ARRAY[1, 2]::integer[]))
sergey-1987@yandex.ru writes:
As I see in documentation,
http://www.postgresql.org/docs/current/static/functions-aggregate.html there
is an ambiguity with bool_or aggregate and ANY array operator, so bool_or
cannot has standard name ANY. So ANY should always mean array operator. But
such query produces syntax error:
select 1 = ANY((select ARRAY[1, 2]::integer[]))
Yes. Do it like this instead:
select 1 = ANY(ARRAY[1, 2]::integer[]);
If the argument of ANY is a sub-select, the interpretation is to compare
the left-hand value to each row of the sub-select's output. This isn't
terribly consistent perhaps, but it's what's required by the SQL
standard.
regards, tom lane