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

I think I got the shortest answer, while still preserving 4 = ALL (ARRAY[4,5]::integer[]); construct:

Live test: https://www.db-fiddle.com/f/6DuB1N4FdcvZdxKiHczu5y/1

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 and 2 can be any two distinct numbers.

Alternatives and performance

There are a couple of ways. I assembled a test case to see which is fastest:

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[])
   , ('{}'::int[])
   ) t1(arr);

The second is from the currently accepted answer by @michael. The columns are in order of performance of the expression. Fastest first. My proposed expression is actually many times faster than the rest. Hence my answer.

Note the varying result for empty array input. (What answer would you expect for an empty array?)

db<>fiddle here - with demo & 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, 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 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

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 5

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;