IS DISTINCT FROM 可以与 ANY 或 ALL 以某种方式结合吗？

将其视为语法问题，ANY定义为（在Row and Array Comparisons中）：

表达式运算符 ANY（数组表达式）

但is distinct from它不是运算符，它是一个“构造”，正如我们在比较运算符中所说的那样：

当这种行为不合适时，使用 IS [ NOT ] DISTINCT FROM 结构

由于 PostgreSQL 有用户定义的运算符，我们可以为此目的定义一个运算符/函数组合：

create function is_distinct_from(text, text) returns bool as 
'select $1 is distinct from $2;' language sql;

create operator <!> (
 procedure=is_distinct_from(text,text),
 leftarg=text, rightarg=text
);

然后它可以先于ANY：

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <!> any(array[null, 'A']);  

数数
--------
     3
(1 行)

操作员

这是建立在@Daniel 聪明的 operator之上的。
在此过程中，使用多态类型创建函数/运算符组合。然后它适用于任何类型 - 就像构造一样。
并制作函数IMMUTABLE。

CREATE FUNCTION is_distinct_from(anyelement, anyelement)
  RETURNS bool LANGUAGE sql IMMUTABLE AS 
'SELECT $1 IS DISTINCT FROM $2';

CREATE OPERATOR <!> (
  PROCEDURE = is_distinct_from(anyelement,anyelement),
  LEFTARG  = anyelement
, RIGHTARG = anyelement
);

使用 symbolhound 进行的快速搜索是空的，因此该运算符<!>似乎没有在任何模块中使用。

如果您打算大量使用此运算符，您可能会进一步充实它以帮助查询规划器（如评论中建议的 losthorse）。对于初学者，您可以添加COMMUTATORandNEGATOR子句来辅助查询优化器。CREATE OPERATOR从上面替换为：

CREATE OPERATOR <!> (
  PROCEDURE = is_distinct_from(anyelement,anyelement),
  LEFTARG  = anyelement
, RIGHTARG = anyelement
, COMMUTATOR = <!>
, NEGATOR = =!=
);

并添加：

CREATE FUNCTION is_not_distinct_from(anyelement, anyelement)
  RETURNS bool LANGUAGE sql IMMUTABLE AS 
'SELECT $1 IS NOT DISTINCT FROM $2';

CREATE OPERATOR =!= (
  PROCEDURE = is_not_distinct_from(anyelement,anyelement),
  LEFTARG  = anyelement
, RIGHTARG = anyelement
, COMMUTATOR = =!=
, NEGATOR = <!>
);

但是附加子句对手头的用例没有帮助，并且仍然不会使用普通索引。实现这一点要复杂得多。（我没试过。）详情请阅读手册中的“运营商优化信息”一章。

测试用例

只有当数组中的所有值都相同时，问题中的测试用例才能成功。对于问题 ( '{null,A}'::text[]) 中的数组，结果始终为 TRUE。这是故意的吗？我为“IS DISTINCT FROM ALL”添加了另一个测试：

SELECT foo
     , foo <!> ANY ('{null,A}'::text[]) AS chk_any
     , foo <!> ALL ('{null,A}'::text[]) AS chk_all
FROM (
   VALUES ('A'),('Z'),(NULL)
   ) z(foo)

 foo | chk_any | chk_all
-----+---------+---------
 A   | t       | f
 Z   | t       | t
     | t       | f

标准运算符的替代方案

foo IS DISTINCT FROM ANY (test_arr) -- illegal syntax

几乎可以翻译成

foo = ALL (test_arr) IS NOT TRUE

foo = ALL (test_arr)产量...

TRUE .. 如果所有元素都是foo
FALSE.. 如果任何NOT NULL元素是<> foo
NULL .. 如果至少一个元素IS NULL并且没有元素是<> foo

所以，剩下的极端情况是
- foo IS NULL
- 并且 test_arr只包含NULL元素。

如果可以排除其中任何一个，我们就完成了。因此，使用简单的测试 if
- 列已定义NOT NULL。
-或者你知道数组永远不会都是 NULL。

否则，额外测试：

AND ('A' = ALL(test_arr) IS NOT NULL OR 
     'B' = ALL(test_arr) IS NOT NULL OR
     foo IS NOT NULL)

Where'A'和'B'可以是任何不同的值。在这个相关问题下的解释和替代方案：
Is array all NULLs in PostgreSQL

同样，如果您知道任何不能存在于 中的值，test_arr例如空字符串''，您仍然可以简化：

AND ('' = ALL(test_arr) IS NOT NULL OR
     foo IS NOT NULL)

这是检查所有组合的完整测试矩阵：

SELECT foo, test_arr
     , foo = ALL(test_arr) IS NOT TRUE  AS test_simple
     , foo = ALL(test_arr) IS NOT TRUE
       AND ('A' = ALL(test_arr) IS NOT NULL OR
            'B' = ALL(test_arr) IS NOT NULL OR 
            foo IS NOT NULL)            AS test_sure 
FROM (
   VALUES ('A'),('Z'),(NULL)
   ) v(foo)
CROSS JOIN (
   VALUES ('{null,A}'::text[]),('{A,A}'),('{null,null}')
   ) t(test_arr)

 foo |  test_arr   | test_simple | test_sure
-----+-------------+-------------+-----------
 A   | {NULL,A}    | t           | t
 A   | {A,A}       | f           | f   -- only TRUE case
 A   | {NULL,NULL} | t           | t
 Z   | {NULL,A}    | t           | t
 Z   | {A,A}       | t           | t
 Z   | {NULL,NULL} | t           | t
     | {NULL,A}    | t           | t
     | {A,A}       | t           | t
     | {NULL,NULL} | t           | f   -- special case

这比Andriy 的EXCEPT解决方案要冗长一些，但速度要快得多。

也许像这样：

select foo
     , exists (values (null), ('A') except select foo) chk_any
     , not exists (values (null), ('A') intersect select foo) chk_all
from ( values ('A'),('Z'),(null) ) z(foo);

 foo | chk_any | chk_all
-----+---------+---------
 A   | t       | f
 Z   | t       | t
     | t       | f

请注意，不仅null在“数组”中，而且在null以z这种方式比较。