检查NULL在Postgres的数组存在 [英] 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)
只有使用 <$ C $绝招C> array_to_string 显示了预期值。有没有更好的方法来测试这一点？
Only a trick with array_to_string shows the expected value. Is there a better way to test this?
推荐答案
如果您的知道的，可以在您的阵列不会存在一个单一的元素，你可以使用这个快速的Postgres里的 9.1 （或的的版本的Postgres）前pression。假设你有正数的数组，所以 1 不能在它：
If you know a single element that can never exist in your arrays, you can use this fast expression in Postgres 9.1 (or any version of Postgres). Say you have an array of positive numbers, so -1 can't be in it:
-1 = ANY(ar) IS NULL
与详细的解释相关答案是：
Related answer with detailed explanation:
- Is数组中的PostgreSQL NULL的所有
- Is array all NULLs in PostgreSQL
如果您的不能绝对确定的，你的可能的回落到昂贵的之一，但的安全的方法与 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(ar) x)
或
EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)
但你可以拥有的快速，安全的有 CASE 前pression。用一个不太可能的数量，回落到安全的方法，如果它应该存在。您可能需要治疗的情况下 AR 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 ar IS NULL separately. See demo below.
Postgres的9.1是老了。考虑升级到最新版本。在Postgres的 9.3 或以后你可以用内置函数的 array_remove（） 或的 array_replace（） 。
Postgres 9.1 is getting old. Consider upgrading to a current version. In Postgres 9.3 or later you can test with the built-in function array_remove() or array_replace() .
或者你可以把它与 <工作code> array_position（） Postgres里的 9.5 或以后类似的 @Patrick提供。我加了以下改进变种。
Or you can make it work with array_position() in Postgres 9.5 or later like @Patrick provided. I added improved variants below.
SELECT num, ar, expect
, -1 = ANY(ar) IS NULL AS t_1 -- 50 ms
, (SELECT bool_or(x IS NULL) FROM unnest(ar) x) AS t_2 -- 754 ms
, EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL) AS t_3 -- 521 ms
, CASE -1 = ANY(ar)
WHEN FALSE THEN FALSE
WHEN TRUE THEN EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)
ELSE NULLIF(ar IS NOT NULL, FALSE) -- catch ar IS NULL -- 55 ms
-- ELSE TRUE -- simpler for columns defined NOT NULL -- 51 ms
END AS t_91
, array_replace(ar, NULL, 0) <> ar AS t_93a -- 99 ms
, array_remove(ar, NULL) <> ar AS t_93b -- 96 ms
, cardinality(array_remove(ar, NULL)) <> cardinality(ar) AS t_94 -- 81 ms
, COALESCE(array_position(ar, NULL::int), 0) > 0 AS t_95a -- 49 ms
, array_position(ar, NULL) IS NOT NULL AS t_95b -- 45 ms
, CASE WHEN ar IS NOT NULL
THEN array_position(ar, 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, ar, expect);
结果：
num | ar | 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（）中不允许多维数组。所有前pressions到右侧 t_93a 仅用于1 dimenstioal数组。
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.
在此 SQL小提琴 更多测试（Postgres的9.3）。
More tests in this SQL Fiddle (for Postgres 9.3).
增加的时间是从Postgres的9.5 基准测试与20万行即可。这是我的设置：
The added times are from a benchmark test with 200k rows in Postgres 9.5. This is my setup:
CREATE TEMP TABLE t AS
SELECT row_number() OVER() AS num
, array_agg(elem) AS ar
, 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 AS
'SELECT array_position($1, NULL) IS NOT NULL';
使用<一href=\"http://www.postgresql.org/docs/current/interactive/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC\"相对=nofollow>多态性输入类型此工程的任何的数组类型，而不仅仅是 INT [] 。
Using a polymorphic input type this works for any array type, not just int[].
请它 IMMUTABLE 让性能优化和指数前pressions。
Make it IMMUTABLE to allow performance optimization and index expressions.
- Does PostgreSQL support "accent insensitive" collations?
但不要让它严格，这将禁用内联函数和损害的表现。
But don't make it STRICT, which would disable "function inlining" and impair performance.
- Function executes faster without STRICT modifier?
如果你需要捕捉的情况下 AR IS NULL ，而不是使功能严格，使用
If you need to catch the case ar IS NULL, instead of making the function STRICT, use:
CREATE OR REPLACE FUNCTION f_array_has_null (anyarray)
RETURNS bool LANGUAGE sql IMMUTABLE AS
'SELECT CASE WHEN $1 IS NOT NULL
THEN array_position($1, NULL) IS NOT NULL END';
有关Postgres的 9.1 使用 t_91 前pression从上面。其余适用不变。
For Postgres 9.1 use the t_91 expression from above. The rest applies unchanged.
与此密切相关的问题：
- How to determine if NULL is contained in an array in Postgres?
这篇关于检查NULL在Postgres的数组存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
