Postgres ANY operator with array selected in a subquery

Question

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

The same results occur for 9.2.9.

Dwayne Towell
– Dwayne Towell

2014-12-12 03:50:04 +00:00
Commented Dec 12, 2014 at 3:50 — Dwayne Towell
– Dwayne Towell, Commented Dec 12, 2014 at 3:50

Community · Accepted Answer · 2020-06-20 09:12:55Z

10

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)
           );

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 12, 2014 at 11:19

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Emery Lapinski Over a year ago

Fascinating and obscure. Thank you for your answer.

Carlana Over a year ago

When I read this, I thought rows() was a keyword, but it's just the way of naming an anonymous table/row. In this case, it's naming the values as a one-off table named rows. This syntax works too: select 1 = any(select mycol from (values (1), (2)) AS mytable (mycol)).

Collectives™ on Stack Overflow

Postgres ANY operator with array selected in a subquery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related