Is array all NULLs in PostgreSQL

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[])
   ) 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.

SQL Fiddle with demo & performance test.

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 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á.

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

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.