postgresql - 在子查询中选择数组的 Postgres ANY 运算符

Question

有人可以向我解释为什么第 4 次选择有效，但前 3 次无效吗？ (如果重要的话，我使用的是 PostgreSQL 9.3.4。)

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

这是一个 sqlfiddle:http://sqlfiddle.com/#!15/56a09/2

Answer 1

您可能期待一个聚合。根据 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.

在 Postgres 中，any 运算符存在 for subqueries和 for arrays .

前三个查询返回一组 int[] 类型的值，您将它们与 int 进行比较。无法工作。

最后一个查询返回一个 int[] 数组，但它之所以有效，是因为您返回的是单个元素。

附件A；这有效:

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

但这不是:

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

结果(相当于您的第四个查询):

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

但这不会(相当于您的第四个查询返回多行):

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

这些也应该有效，顺便说一句:

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

另请注意 array(select ...)) 结构，因为它偶尔会派上用场:

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