CODEWITHSUNDEEP
postgresql
Emery Lapinski
Emery Lapinski

Reputation: 1662

Postgres ANY operator with array selected in a subquery

Can someone explain to me why the 4th select works, but the first 3 do not? (I'm on PostgreSQL 9.3.4 if it matters.) 

drop table if exists temp_a;
create temp table temp_a as
(
    select array[10,20] as arr
);

select 10 = any(select arr from temp_a);    -- ERROR:  operator does not exist: integer = integer[]

select 10 = any(select arr::integer[] from temp_a); -- ERROR:  operator does not exist: integer = integer[]

select 10 = any((select arr from temp_a));  -- ERROR:  operator does not exist: integer = integer[]

select 10 = any((select arr from temp_a)::integer[]);   -- works

Here's a sqlfiddle: http://sqlfiddle.com/#!15/56a09/2

Upvotes: 10

Views: 9210

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78513

You might be expecting an aggregate. Per the documentation:

Note: Boolean aggregates bool_and and bool_or correspond to standard SQL aggregates every and any or some. As for any and some, it seems that there is an ambiguity built into the standard syntax:

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

Here ANY can be considered either as introducing a subquery, or as being an aggregate function, if the subquery returns one row with a Boolean value. Thus the standard name cannot be given to these aggregates.

In Postgres, the any operator exists for subqueries and for arrays.

The first three queries return a set of values of type int[] and you're comparing them to an int. Can't work.

The last query is returning an int[] array but it's only working because you're returning a single element.

Exhibit A; this works:

select (select i from (values (array[1])) rows(i))::int[];

But this doesn't:

select (select i from (values (array[1]), (array[2])) rows(i))::int[];

This works as a result (equivalent to your fourth query):

select 1 = any((select i from (values (array[1])) rows(i))::int[]);

But this doesn't (equivalent to your fourth query returning multiple rows):

select 1 = any((select i from (values (array[1]), (array[2])) rows(i))::int[]);

These should also work, btw:

select 1 = any(
             select unnest(arr) from temp_a
           );
select 1 = any(
             select unnest(i)
             from (values (array[1]), (array[2])) rows(i)
           );

Also note the array(select ...)) construct as an aside, since it's occasionally handy:

select 1 = any(array(
             select i
             from (values (1), (2)) rows(i)
           ));
select 1 = any(
             select i
             from (values (1), (2)) rows(i)
           );

Upvotes: 10

Related Questions