Как определить, содержится ли NULL в массиве в Postgres?

Question

Как определить, содержится ли NULL в массиве в Postgres? В настоящее время использую Postgres 9.3.3.

Если я тестирую со следующим выбором, он возвращает contains_null = false,

select ARRAY[NULL,1,2,3,4,NULL]::int[] @> ARRAY[NULL]::int[] AS contains_null
select ARRAY[NULL,1,2,3,4,NULL]::int[] @> NULL AS contains_null

Я также попробовал с:

1. @> (содержит)
2. <@ (содержится в)
3. && (перекрытие)

Answer 1

select exists (
    select 1 
    from unnest(array[1, null]) s(a)
    where a is null
);
 exists 
--------
 t

Или короче:

select bool_or(a is null)
from unnest(array[1, null]) s(a)
;
 bool_or 
---------
 t

Answer 2

Еще одна конструкция, например, @Clodoaldo Neto. Просто более компактное выражение:

CREATE TEMPORARY TABLE null_arrays (
      id serial primary key
    , array_data int[]
);

INSERT INTO null_arrays (array_data)
VALUES
      (ARRAY[1,2, NULL, 4, 5])
    , (ARRAY[1,2, 3, 4, 5])
    , (ARRAY[NULL,2, 3, NULL, 5])
;

SELECT 
    *
FROM 
    null_arrays
WHERE
    TRUE = ANY (SELECT unnest(array_data) IS NULL)
;

Answer 3

Похоже, что в PostgreSQL 10.1 все работает нормально.

CREATE TABLE my_table
(
    ...
    my_set  int[] NOT NULL,
    ...
);

SELECT
    my_set
FROM
    my_table
WHERE
    array_position(my_set, NULL) IS NOT NULL;

Answer 4

В идеале вы должны написать:

SELECT
    NULL IS NOT DISTINCT FROM ANY ARRAY[NULL,1,2,3,4,NULL]::int[];

но парсер не распознает IS NOT DISTINCT FROM как действительный синтаксис для оператора здесь, и я не могу найти псевдоним оператора для него.

Вы должны были бы:

CREATE FUNCTION opr_isnotdistinctfrom(anyelement, anyelement)
RETURNS boolean LANGUAGE SQL IMMUTABLE AS $$
SELECT $1 IS NOT DISTINCT FROM $2; 
$$;

CREATE OPERATOR <<>> (
    PROCEDURE = opr_isnotdistinctfrom,
    LEFTARG = anyelement,
    RIGHTARG = anyelement
);

SELECT NULL <<>> ANY (ARRAY[NULL,1,2,3,4,NULL]::int[]);

который кажется немного ужасным, но должен оптимизироваться просто отлично.

Answer 5

      SELECT array_position(ARRAY[1,2,3,NULL], NULL)

возвращает 4 (позиция NULL) возвращает NULL, если элемент не найден

Answer 6

Я не хотел использовать unnest либо, поэтому я использовал сравнение array_length с помощью array_remove решить аналогичную проблему. Проверено на 9.4.1, но должно работать в 9.3.3.

SELECT
ARRAY_LENGTH(ARRAY[1,null], 1) > ARRAY_LENGTH(ARRAY_REMOVE(ARRAY[1,null], NULL), 1) 
OR ARRAY_LENGTH(ARRAY_REMOVE(ARRAY[1,null], NULL), 1) IS NULL
---------
t

Answer 7

Вот это как функция многократного использования:

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

Затем вы можете использовать его в проверочном ограничении:

      CREATE TABLE foo (
  array_with_no_nulls TEXT[] NOT NULL CHECK(f_check_no_null(array_with_no_nulls))
);