检查Postgres数组中是否存在NULL

Question

与此问题类似，如何查找数组中是否存在空值？

这里有一些尝试。

SELECT num, ar, expected,
  ar @> ARRAY[NULL]::int[] AS test1,
  NULL = ANY (ar) AS test2,
  array_to_string(ar, ', ') <> array_to_string(ar, ', ', '(null)') AS test3
FROM (
  SELECT 1 AS num, '{1,2,NULL}'::int[] AS ar, true AS expected
  UNION SELECT 2, '{1,2,3}'::int[], false
) td ORDER BY num;

 num |     ar     | expected | test1 | test2 | test3
-----+------------+----------+-------+-------+-------
   1 | {1,2,NULL} | t        | f     |       | t
   2 | {1,2,3}    | f        | f     |       | f
(2 rows)

只有使用array\u to\u string的技巧才会显示预期值。有没有更好的方法来测试这一点？

Answer 1

PostgreSQL 9.5（我知道你定义了9.1，但无论如何）具有array_position（）函数，可以做你想做的事情，而不必使用非常低效的unnest（）来处理像这样琐碎的事情（参见test4）：

patrick@puny:~$ psql -d test
psql (9.5.0)
Type "help" for help.

test=# SELECT num, ar, expected,
  ar @> ARRAY[NULL]::int[] AS test1,
  NULL = ANY (ar) AS test2,
  array_to_string(ar, ', ') <> array_to_string(ar, ', ', '(null)') AS test3,
  coalesce(array_position(ar, NULL::int), 0) > 0 AS test4
FROM (
  SELECT 1 AS num, '{1,2,NULL}'::int[] AS ar, true AS expected
  UNION SELECT 2, '{1,2,3}'::int[], false
) td ORDER BY num;
 num |     ar     | expected | test1 | test2 | test3 | test4
-----+------------+----------+-------+-------+-------+-------
   1 | {1,2,NULL} | t        | f     |       | t     | t
   2 | {1,2,3}    | f        | f     |       | f     | f
(2 rows)

Answer 2

PostgreSQL的UNNEST（）函数是更好的选择。您可以编写下面这样的简单函数来检查数组中的空值。

create or replace function NULL_EXISTS(val anyelement) returns boolean as
$$
select exists (
    select 1 from unnest(val) arr(el) where el is null
);
$$
language sql 

例如

SELECT NULL_EXISTS(array [1,2,NULL])
      ,NULL_EXISTS(array [1,2,3]);

结果：

null_exists null_exists 
----------- -------------- 
t           f     

因此，您可以在如下查询中使用NULL\u EXISTS（）函数。

SELECT num, ar, expected,NULL_EXISTS(ar)
FROM (
  SELECT 1 AS num, '{1,2,NULL}'::int[] AS ar, true AS expected
  UNION SELECT 2, '{1,2,3}'::int[], false
) td ORDER BY num;

Answer 3

或者使用array\u position（）。基本上：

SELECT array_position(arr, NULL) IS NOT NULL AS array_has_null

请参见下面的演示。

您可以使用内置函数进行测试。

如果你知道一个永远不可能存在于数组中的元素，你可以使用这个快速表达式。比如说，你有一个正数数组，-1永远不能在其中：

-1 = ANY(arr) IS NULL

相关答案及详细说明：

• PostgreSQL中的数组均为Null

如果您不能绝对确定，您可以使用unnest（）回到昂贵但安全的方法之一。像：

(SELECT bool_or(x IS NULL) FROM unnest(arr) x)

或：

EXISTS (SELECT 1 FROM unnest(arr) x WHERE x IS NULL)

但是，您可以使用一个快速且安全的大小写表达式。使用一个不太可能的数字，如果应该存在，则使用安全方法。您可能需要单独处理arr为空的情况。请参见下面的演示。

SELECT num, arr, expect
     , -1 = ANY(arr) IS NULL                                    AS t_1   --  50 ms
     , (SELECT bool_or(x IS NULL) FROM unnest(arr) x)           AS t_2   -- 754 ms
     , EXISTS (SELECT 1 FROM unnest(arr) x WHERE x IS NULL)     AS t_3   -- 521 ms
     , CASE -1 = ANY(arr)
         WHEN FALSE THEN FALSE
         WHEN TRUE THEN EXISTS (SELECT 1 FROM unnest(arr) x WHERE x IS NULL)
         ELSE NULLIF(arr IS NOT NULL, FALSE)  -- catch arr IS NULL       --  55 ms
      -- ELSE TRUE  -- simpler for columns defined NOT NULL              --  51 ms
       END                                                      AS t_91
     , array_replace(arr, NULL, 0) <> arr                       AS t_93a --  99 ms
     , array_remove(arr, NULL) <> arr                           AS t_93b --  96 ms
     , cardinality(array_remove(arr, NULL)) <> cardinality(arr) AS t_94  --  81 ms
     , COALESCE(array_position(arr, NULL::int), 0) > 0          AS t_95a --  49 ms
     , array_position(arr, NULL) IS NOT NULL                    AS t_95b --  45 ms
     , CASE WHEN arr IS NOT NULL
            THEN array_position(arr, NULL) IS NOT NULL END      AS t_95c --  48 ms
FROM  (
   VALUES (1, '{1,2,NULL}'::int[], true)     -- extended test case
        , (2, '{-1,NULL,2}'      , true)
        , (3, '{NULL}'           , true)
        , (4, '{1,2,3}'          , false)
        , (5, '{-1,2,3}'         , false)
        , (6, NULL               , null)
   ) t(num, arr, expect);

结果：

 num |  arr        | expect | t_1    | t_2  | t_3 | t_91 | t_93a | t_93b | t_94 | t_95a | t_95b | t_95c
-----+-------------+--------+--------+------+-----+------+-------+-------+------+-------+-------+-------
   1 | {1,2,NULL}  | t      | t      | t    | t   | t    | t     | t     | t    | t     | t     | t
   2 | {-1,NULL,2} | t      | f --!! | t    | t   | t    | t     | t     | t    | t     | t     | t
   3 | {NULL}      | t      | t      | t    | t   | t    | t     | t     | t    | t     | t     | t
   4 | {1,2,3}     | f      | f      | f    | f   | f    | f     | f     | f    | f     | f     | f
   5 | {-1,2,3}    | f      | f      | f    | f   | f    | f     | f     | f    | f     | f     | f
   6 | NULL        | NULL   | t --!! | NULL | f   | NULL | NULL  | NULL  | NULL | f     | f     | NULL

请注意，array_remove（）和array_position（）不允许用于多维数组。t_93a右侧的所有表达式仅适用于一维数组。

DB

增加的次数来自Postgres 9.5中的一个基准测试，测试行数为200000行。这是我的设置：

CREATE TABLE t AS
SELECT row_number() OVER() AS num
     , array_agg(elem) AS arr
     , bool_or(elem IS NULL) AS expected
FROM  (
   SELECT CASE WHEN random() > .95 THEN NULL ELSE g END AS elem  -- 5% NULL VALUES
        , count(*) FILTER (WHERE random() > .8)
                   OVER (ORDER BY g) AS grp  -- avg 5 element per array
   FROM   generate_series (1, 1000000) g  -- increase for big test case
   ) sub
GROUP  BY grp;

为了重复使用，我会在Postgres 9.5中创建如下函数：

CREATE OR REPLACE FUNCTION f_array_has_null (anyarray)
  RETURNS bool
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
 'SELECT array_position($1, NULL) IS NOT NULL';

仅适用于Postgres 9.6或更高版本。

使用多态输入类型，这适用于任何数组类型，而不仅仅是int[]。

使其不可变，以允许性能优化和索引表达式。

• PostgreSQL是否支持“不区分重音”的排序规则

但不要使其严格，这会禁用“函数内联”并损害性能，因为array\u position（）本身并不严格。请参见：

• 函数在没有严格修饰符的情况下执行得更快

如果需要捕获案例，arr为空：

CREATE OR REPLACE FUNCTION f_array_has_null (anyarray)
  RETURNS bool
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
 'SELECT CASE WHEN $1 IS NOT NULL
              THEN array_position($1, NULL) IS NOT NULL END';

对于Postgres 9.1，使用上面的表达式。其余部分不变。

密切相关：

• 如何确定Postgres中的数组中是否包含NULL