Is array all NULLs in PostgreSQL

Question

Is there an expression that returns TRUE if all the elements of a PostgreSQL array are NULL?

If it was a value other than NULL, I could of course use something like:

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

However I want to do the ALL operation with an IS NULL test, rather than a = 4 test. I don't think there's an ALL syntax for this, and the semantics around NULL are compounded with arrays I've not myself been able to think of a form that achieves it. Hence my asking Stack Overflow. ;-)

I know I could write a function in pl/sql or pl/pgsql that does this, but I'd like to see if there's a direct expression before resorting to that.

Answer 1

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

1 and 2 can be any two distinct numbers.

Alternatives and performance

There are many ways. I assembled a quick test case:

SELECT arr::text
     , -1 = ALL(arr) IS NULL                              AS xsimple
     , 1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL      AS simple
     , array_remove(arr, NULL) = '{}'                     AS array_rem
     , cardinality(array_positions(arr, NULL))
     = cardinality(arr)                                   AS array_pos
     , TRUE = ALL (SELECT unnest(arr) IS NULL)            AS michael
     , (SELECT bool_and(e IS NULL) FROM unnest(arr) e)    AS bool_and
     , NOT EXISTS (SELECT unnest(arr) EXCEPT SELECT null) AS exist
FROM  (
   VALUES
     ('{1,2,NULL,3}'::int[])
   , ('{1,1,1}')
   , ('{2,2,2}')
   , ('{NULL,NULL,NULL}')
   , ('{}'::int[])
   ) t(arr);

       arr        | xsimple | simple | array_rem | array_pos | michael | bool_and | exist 
------------------+---------+--------+-----------+-----------+---------+----------+-------
 {1,2,NULL,3}     | f       | f      | f         | f         | f       | f        | f
 {1,1,1}          | f       | f      | f         | f         | f       | f        | f
 {2,2,2}          | f       | f      | f         | f         | f       | f        | f
 {NULL,NULL,NULL} | t       | t      | t         | t         | t       | t        | t
 {}               | f       | f      | t         | t         | t       |          | t

array_remove() requires Postgres 9.3 or later.
array_positions() requires Postgres 9.5 or later.

chk_michael is from the currently accepted answer by @michael.
The columns are in order of performance of the expression. Fastest first.
My simple checks dominate performance, with array_remove() next. The rest cannot keep up.

The special case empty array ({}) requires attention. Define the expected result and either pick a fitting expression or add an additional check.

db<>fiddle here - with performance test
_{Old sqlfiddle}

How does it work?

The expression 1 = ALL(arr) yields:

TRUE .. if all elements are 1
FALSE .. if any element is <> 1 (any element that IS NOT NULL)
NULL .. if at least one element IS NULL and no element is <> 1

So, if we know a single element that cannot show up (enforced by a CHECK constraint), like -1, we can simplify to:

-1 = ALL(arr) IS NULL

If any number can show up, check for two distinct numbers. The result can only be NULL for both if the array contains nothing but NULL. Voilá.

Answer 2

Another approach to make the code shorter, use EVERY aggregate function

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

Output:

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

Another approach, generating NULLs to be used for comparison:

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

Underlying logic of the code above, this returns true:

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

Another approach, use array_fill

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

Caveat, array construct and array_fill are not symmetrical though, test these:

select array[5]

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

Answer 3

I'm not exactly proud of this but:

=> 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)

Yes, there are subqueries galore but maybe you can make it work or simplify it into something that will work.

Answer 4

Just for the sake of variety of options, what I've used before for this is:

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

This method will also return true for no values at all in the array, which is useful when preferring to store a null value instead an empty or all nulls array, eg in an on update trigger:

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