Проверьте, существует ли NULL в массиве Postgres
Подобно этому вопросу, как я могу найти, если в массиве существует значение NULL?
Вот несколько попыток.
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 показывает ожидаемое значение. Есть ли лучший способ проверить это?
3 ответа
Если вы знаете один элемент, который никогда не может существовать в ваших массивах, вы можете использовать это быстрое выражение в Postgres 9.1 (или в любой версии Postgres). Скажем, у вас есть массив положительных чисел, так -1 не может быть в этом:
-1 = ANY(ar) IS NULL
Соответствующий ответ с подробным объяснением:
Если вы не можете быть абсолютно уверены, вы можете прибегнуть к одному из дорогих, но безопасных методов с unnest(), Подобно:
(SELECT bool_or(x IS NULL) FROM unnest(ar) x)
или же:
EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)
Но вы можете иметь быстрый и безопасный с CASE выражение. Используйте маловероятное число и вернитесь к безопасному методу, если он должен существовать. Вы можете рассмотреть дело ar IS NULL по отдельности. Смотрите демо ниже.
Postgres 9.1 стареет. Рассмотрите возможность обновления до текущей версии. В Postgres 9.3 или более поздней версии вы можете протестировать с помощью встроенной функции array_remove() или же array_replace(),
Или вы можете заставить его работать с array_position() в Postgres 9.5 или более поздней версии, например @Patrick. Я добавил улучшенные варианты ниже.
демонстрация
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);
Результат:
число | ар | ожидать | 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
2 | {-1,NULL,2} | т | ф -!! | т | т | т | т | т | т | т | т | T
3 | {NULL} | т | т | т | т | т | т | т | т | т | т | T
4 | {1,2,3} | f | f | f | f | f | f | f | f | f | f | е
5 | {-1,2,3} | f | f | f | f | f | f | f | f | f | f | е
6 | NULL | NULL | т -!! | NULL | f | NULL | NULL | NULL | NULL | f | f | НОЛЬ Обратите внимание, что array_remove() а также array_position() не допускаются для многомерных массивов. Все выражения справа от t_93a работают только для одномерных массивов.
Дополнительные тесты в этой скрипте SQL (для Postgres 9.3).
Настройка бенчмарка
Добавленные времена взяты из теста производительности с 200 тыс. Строк в Postgres 9.5. Это моя установка:
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 следующим образом:
CREATE OR REPLACE FUNCTION f_array_has_null (anyarray)
RETURNS bool LANGUAGE sql IMMUTABLE AS
'SELECT array_position($1, NULL) IS NOT NULL';
Используя полиморфный тип ввода, это работает для любого типа массива, а не только int[],
Сделай это IMMUTABLE разрешить оптимизацию производительности и индексные выражения.
Но не делай этого STRICT, который отключил бы "функцию встраивания" и ухудшил производительность.
Если вам нужно поймать дело ar IS NULL вместо того, чтобы делать функцию STRICT, используйте:
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 выражение сверху. Остальное применяется без изменений.
Тесно связанный вопрос:
Функция UNNEST () в PostgreSQL - лучший выбор. Вы можете написать простую функцию, подобную приведенной ниже, для проверки значений NULL в массиве.
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_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;
PostgreSQL 9.5 (я знаю, что вы spcified 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)
Я использую это
select
array_position(array[1,null], null) is not null
array_position - возвращает индекс первого вхождения второго аргумента в массиве, начиная с элемента, указанного третьим аргументом, или с первого элемента (массив должен быть одномерным)