sql - PostgreSQL 中数组是否全部为 NULL

Question

是否有一个表达式在 PostgreSQL 数组的所有元素都为 NULL 时返回 TRUE？

如果它是 NULL 以外的值，我当然可以使用类似这样的值:

SELECT 4 = ALL (ARRAY[4,5]::integer[]);

但是我想用 IS NULL 测试而不是 = 4 测试来执行 ALL 操作。我不认为有一个 ALL 语法，围绕 NULL 的语义与数组混合在一起，我自己还没有想到实现它的形式。因此我问 Stack Overflow。 ;-)

我知道我可以在 pl/sql 或 pl/pgsql 中编写一个函数来执行此操作，但我想在使用它之前先看看是否有直接表达式。

Answer 1

<b>1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL</b>

1和 2可以是任何两个不同的数字。

备选方案和性能

方法有很多种。我组装了一个快速测试用例:

SELECT arr::text
     , -1 = ALL(arr) IS NULL                              AS xsimple
     , 1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL      AS simple
     , array_remove(arr, NULL) = '{}'                     AS array_rem
     , cardinality(array_positions(arr, NULL))
     = cardinality(arr)                                   AS array_pos
     , TRUE = ALL (SELECT unnest(arr) IS NULL)            AS michael
     , (SELECT bool_and(e IS NULL) FROM unnest(arr) e)    AS bool_and
     , NOT EXISTS (SELECT unnest(arr) EXCEPT SELECT null) AS exist
FROM  (
   VALUES
     ('{1,2,NULL,3}'::int[])
   , ('{1,1,1}')
   , ('{2,2,2}')
   , ('{NULL,NULL,NULL}')
   , ('{}'::int[])
   ) t(arr);

       arr        | xsimple | simple | array_rem | array_pos | michael | bool_and | exist 
------------------+---------+--------+-----------+-----------+---------+----------+-------
 {1,2,NULL,3}     | f       | f      | f         | f         | f       | f        | f
 {1,1,1}          | f       | f      | f         | f         | f       | f        | f
 {2,2,2}          | f       | f      | f         | f         | f       | f        | f
 {NULL,NULL,NULL} | t       | t      | t         | t         | t       | t        | t
 {}               | f       | f      | t         | t         | t       |          | t

array_remove() 需要 Postgres 9.3 或更高版本。
array_positions() 需要 Postgres 9.5 或更高版本。

chk_michael来自currently accepted answer by @michael .
列按表达式的执行顺序排列。最快的优先。
我的简单检查控制性能，使用 array_remove()下一个。其余的跟不上。

特例空数组 ( {} ) 需要注意。定义预期结果并选择合适的表达式或添加额外的检查。

db<> fiddle here - 带有性能测试
<子>旧sqlfiddle

它是如何工作的？

表达式1 = ALL(arr)产量:

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

因此，如果我们知道一个不能显示的元素(由 CHECK 约束强制执行)，例如 -1 ，我们可以简化为:

-1 = ALL(arr) IS NULL

如果可以显示任何 数字，请检查两个不同的数字。结果只能是NULL如果数组只包含 NULL，则两者都包含.瞧。