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

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.

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

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;