检查 Postgres 数组中是否存在 NULL [英] Check if NULL exists in Postgres array
问题描述
类似于这个问题，如何判断数组中是否存在NULL值?
Similar to this question, how can I find if a NULL value exists in an array?
这里有一些尝试.
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_to_string 显示预期值.有没有更好的方法来测试这个?
Only a trick with array_to_string shows the expected value. Is there a better way to test this?
推荐答案
Postgres 9.5 或更高版本
或者使用array_position().基本上:
SELECT array_position(arr, NULL) IS NOT NULL AS array_has_null
见下面的演示.
您可以使用内置函数进行测试 array_remove() 或 array_replace().
You can test with the built-in functions array_remove() or array_replace().
如果您知道一个永远不会存在于您的数组中的单个元素，您可以使用这个fast表达式.假设您有一个正数数组，-1 永远不会在其中:
If you know a single element that can never exist in your arrays, you can use this fast expression. Say, you have an array of positive numbers, and -1 can never be in it:
-1 = ANY(arr) IS NULL
相关回答并有详细说明:
Related answer with detailed explanation:
如果您不能绝对确定，您可以使用unnest()返回到昂贵但安全的方法之一.喜欢:
If you cannot be absolutely sure, you could fall back to one of the expensive but safe methods with unnest(). Like:
(SELECT bool_or(x IS NULL) FROM unnest(arr) x)
或:
EXISTS (SELECT 1 FROM unnest(arr) x WHERE x IS NULL)
但是您可以使用 CASE 表达式快速和安全.使用一个不太可能的数字，如果它应该存在，则退回到安全的方法.您可能需要单独处理 arr IS NULL 的情况.请参阅下面的演示.
But you can have fast and safe with a CASE expression. Use an unlikely number and fall back to the safe method if it should exist. You may want to treat the case arr IS NULL separately. See demo below.
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 右侧的所有表达式仅适用于一维数组.
Note that array_remove() and array_position() are not allowed for multi-dimensional arrays. All expressions to the right of t_93a only work for 1-dimenstioal arrays.
db<>fiddle 这里Postgres 13，有更多测试
旧sqlfiddle
添加的时间来自 Postgres 9.5 中 20 万行的基准测试.这是我的设置:
The added times are from a benchmark test with 200k rows in Postgres 9.5. This is my setup:
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 中创建一个函数，如下所示:
Function wrapper
For repeated use, I would create a function in Postgres 9.5 like this:
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';
PARALLEL SAFE 仅适用于 Postgres 9.6 或更高版本.
PARALLEL SAFE only for Postgres 9.6 or later.
使用多态输入类型这适用于任何数组类型，而不仅仅是int[].
Using a polymorphic input type this works for any array type, not just int[].
使其IMMUTABLE 允许性能优化和索引表达式.
Make it IMMUTABLE to allow performance optimization and index expressions.
但不要让它成为STRICT，这会禁用函数内联"并损害性能，因为 array_position() 不是 STRICT 本身.见:
But don't make it STRICT, which would disable "function inlining" and impair performance because array_position() is not STRICT itself. See:
如果你需要捕捉案例arr IS NULL:
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，使用上面的 t_91 表达式.其余不变.
For Postgres 9.1 use the t_91 expression from above. The rest applies unchanged.
密切相关:
这篇关于检查 Postgres 数组中是否存在 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
