Postgresql unnest function can do many wonders

I saw an interesting Postgres question on Stackoverflow

How to know if all the elements of the array are NULL?

This code works if you want to test if all the elements of the array are number 8, but doesn't work on NULLs:

SELECT 8 = ALL(ARRAY[8,8,8,8]::int[]); -- returns true

Unfortunately, you cannot use that same construct for comparing nulls, this return null:

SELECT NULL = ALL(ARRAY[NULL,NULL,NULL,NULL]::int[]);

And this returns a syntax error:

SELECT NULL IS ALL(ARRAY[NULL,NULL,NULL,NULL]::integer[]);

Fortunately, there's a function to un-nest an array to row elements; then from there, we can use the ALL construct for rows

So given this:

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

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

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

q_and_a=# select * from x;
 y |           z
---+-----------------------
 1 | {NULL,NULL,NULL}
 2 | {NULL,7,NULL}
 3 | {NULL,3,4}
 4 | {NULL,NULL,NULL,NULL}
 5 | {8,8,8,8}
(5 rows)

Then let's try to unnest array z from x:

q_and_a=# select *, unnest(z) from x;
 y |           z           | unnest
---+-----------------------+--------
 1 | {NULL,NULL,NULL}      |
 1 | {NULL,NULL,NULL}      |
 1 | {NULL,NULL,NULL}      |
 2 | {NULL,7,NULL}         |
 2 | {NULL,7,NULL}         |      7
 2 | {NULL,7,NULL}         |
 3 | {NULL,3,4}            |
 3 | {NULL,3,4}            |      3
 3 | {NULL,3,4}            |      4
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
(17 rows)

We can compare the element directly from select clause:

q_and_a=# select y, unnest(z), unnest(z) is null from x;
 y | unnest | ?column?
---+--------+----------
 1 |        | t
 1 |        | t
 1 |        | t
 2 |        | t
 2 |      7 | f
 2 |        | t
 3 |        | t
 3 |      3 | f
 3 |      4 | f
 4 |        | t
 4 |        | t
 4 |        | t
 4 |        | t
 5 |      8 | f
 5 |      8 | f
 5 |      8 | f
 5 |      8 | f
(17 rows)

Now, given that information on hand, we could proceed with this query:

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
 5 | f
(5 rows)

But the following is a lot better than that:

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

Output:

 y | ?column?
---+----------
 1 | t
 2 | f
 3 | f
 4 | t
 5 | f
(5 rows)

If you want to make your query flexible(e.g. by way of concatenated query), you could do the following:

select
y, coalesce( true = ALL (select b is null from unnest(z) as a(b)), false )
from x

Then you can change the expression b is null to something else:

select
y, coalesce( true = ALL (select b = 8 from unnest(z) as a(b)), false )
from x