Массив всех NULL в PostgreSQL

Question

Есть ли выражение, которое возвращает TRUE, если все элементы массива PostgreSQL равны NULL?

Если бы это было значение, отличное от NULL, я, конечно, мог бы использовать что-то вроде:

SELECT 4 = ALL (ARRAY[4,5]::integer[]);

Однако я хочу сделать операцию ВСЕ с IS NULL тест, а не = 4 тестовое задание. Я не думаю, что для этого есть синтаксис ALL, а семантика вокруг NULL состоит из массивов, которые я сам не мог придумать для формы, которая этого добивается. Отсюда мой вопрос переполнения стека.;-)

Я знаю, что мог бы написать функцию в pl / sql или pl / pgsql, которая делает это, но я хотел бы посмотреть, есть ли прямое выражение, прежде чем прибегать к этому.

Answer 1

Я думаю, что я получил самый короткий ответ, сохраняя при этом 4 = ALL (ARRAY[4,5]::integer[]); построить:

select
y, true = ALL (select unnest(z) is null)
from x

Answer 2

1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL

1 а также 2 могут быть любые два разных числа.

Альтернативы и производительность

Есть несколько способов. Я собрал тестовый пример, чтобы увидеть, какой из них самый быстрый:

SELECT arr
     , 1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL      AS chk_simpl
     , TRUE = ALL (SELECT unnest(arr) IS NULL)            AS chk_michael
     , (SELECT bool_and(e IS NULL) FROM unnest(arr) e)    AS chk_bool_and
     , NOT EXISTS (SELECT unnest(arr) EXCEPT SELECT null) AS chk_exist
FROM  (
   VALUES
     ('{[1,2,NULL,3}'::int[])
    ,('{1,1,1}')
    ,('{2,2,2}')
    ,('{NULL,NULL,NULL]}'::int[])
   ) t1(arr);

Второй из принятого в настоящее время ответа @michael. Столбцы расположены в порядке выполнения выражения. Самый быстрый первый. Мое предлагаемое выражение на самом деле во много раз быстрее, чем остальные. Отсюда и мой ответ.

SQL Fiddle с демонстрацией и тестом производительности.

Как это работает?

Выражение 1 = ALL(arr) доходность

TRUE.. если все элементы 1
FALSE.. если какой-либо элемент <> 1 (любой элемент, который IS NOT NULL)
NULL.. если хотя бы один элемент IS NULL и нет элемента <> 1

Итак, если мы знаем один элемент, который не может появиться, как -1 Мы можем упростить до:

-1 = ALL(arr) IS NULL

Если какое-либо число может появиться, проверьте наличие двух разных номеров. Результатом может быть только NULL для обоих, если массив не содержит ничего, кроме NULL, Вуаля.

Answer 3

Я не совсем горжусь этим, но:

=> select not exists (
    select 1
    from (select all unnest(ARRAY[NULL, NULL, NULL]) is null as x) as dt
    where x = 'f'
);
 ?column? 
----------
 t
(1 row)

=> select not exists (
    select 1
    from (select all unnest(ARRAY[NULL, 11, NULL]) is null as x) as dt
    where x = 'f'
);
 ?column? 
----------
 f
(1 row)

Да, существует множество подзапросов, но, возможно, вы можете заставить его работать или упростить его до того, что будет работать.

Answer 4

Другой подход, чтобы сделать код короче, использовать КАЖДУЮ агрегатную функцию

create table x
(
y serial,
z int[]
);

insert into x(z) values(array[null,null,null]::int[])
insert into x(z) values(array[null,7,null]::int[])
insert into x(z) values(array[null,3,4]::int[])
insert into x(z) values(array[null,null,null,null]::int[])


with a as
(
    select y, unnest(z) as b
    from x
)
select y, every(b is null)
from a 
group by y
order by y

Выход:

 y | every
---+-------
 1 | t
 2 | f
 3 | f
 4 | t
(4 rows)

Другой подход, генерирующий значения NULL для сравнения:

select  y, 
    z = 
    (select array_agg(null::int) 
     from generate_series(1, array_upper(z, 1) )) as IsAllNulls
from    x

Основная логика приведенного выше кода возвращает true:

SELECT ARRAY[NULL,NULL]::int[] = ARRAY[NULL,NULL]::int[]

Другой подход, использовать array_fill

select  y, z = array_fill(null::int, array[ array_upper(z, 1) ] )
from    x

Caveat, конструкция массива и array_fill не являются симметричными, проверьте это:

select array[5]

-- array[5] here has different meaning from array[5] above
select array_fill(null::int, array[5]) 

Answer 5

Просто ради разнообразия опций я использовал для этого:

select array_remove(ARRAY[null::int, null, null], null) = '{}'

Этот метод также возвращает true, если в массиве вообще нет значений, что полезно, когда предпочитается хранить пустое значение вместо пустого или полностью пустого массива, например, в триггере при обновлении:

NEW.arrvalue := CASE WHEN array_remove(NEW.arrvalue, null) <> '{}' THEN NEW.arrvalue END;