Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- big table for testing performance
CREATE TABLE t AS
SELECT ARRAY[i,i,NULL,i] AS arr     FROM generate_series(1, 10000) i
UNION ALL
SELECT ARRAY[1,1,1]                 FROM generate_series(1, 10000) i
UNION ALL
SELECT ARRAY[2,2,2]                 FROM generate_series(1, 10000) i
UNION ALL
SELECT ARRAY[NULL,NULL,NULL]::int[] FROM generate_series(1, 10000) i;
40000 rows affected

VACUUM ANALYZE t;
-- Demo
SELECT arr::text
     , 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);


arr
chk_simpl
chk_michael
chk_bool_and
chk_exist


{1,2,NULL,3}
f
f
f
f

{1,1,1}
f
f
f
f

{2,2,2}
f
f
f
f

{NULL,NULL,NULL}
t
t
t
t

{}
f
t
null
t


-- Performance
EXPLAIN (ANALYZE, TIMING OFF)
SELECT 1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL      AS chk_simpl
FROM   t;


QUERY PLAN


Seq Scan on t  (cost=0.00..1734.00 rows=40000 width=1) (actual rows=40000 loops=1)

Planning Time: 0.057 ms

Execution Time: 8.820 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT TRUE = ALL (SELECT unnest(arr) IS NULL)            AS chk_michael
FROM   t;


QUERY PLAN


Seq Scan on t  (cost=0.00..5084.00 rows=40000 width=1) (actual rows=40000 loops=1)

  SubPlan 1

    ->  Result  (cost=0.00..0.19 rows=10 width=1) (actual rows=2 loops=40000)

          ->  ProjectSet  (cost=0.00..0.07 rows=10 width=4) (actual rows=2 loops=40000)

                ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=40000)

Planning Time: 0.043 ms

Execution Time: 30.601 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT (SELECT bool_and(e IS NULL) FROM unnest(arr) e)    AS chk_bool_and
FROM   t;


QUERY PLAN


Seq Scan on t  (cost=0.00..6234.00 rows=40000 width=1) (actual rows=40000 loops=1)

  SubPlan 1

    ->  Aggregate  (cost=0.13..0.14 rows=1 width=1) (actual rows=1 loops=40000)

          ->  Function Scan on unnest e  (cost=0.00..0.10 rows=10 width=4) (actual rows=3 loops=40000)

Planning Time: 0.053 ms

Execution Time: 101.271 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT NOT EXISTS (SELECT unnest(arr) EXCEPT SELECT null) AS chk_exist
FROM   t;


QUERY PLAN


Seq Scan on t  (cost=0.00..1814.00 rows=40000 width=1) (actual rows=40000 loops=1)

  SubPlan 1

    ->  HashSetOp Except  (cost=0.00..0.27 rows=10 width=8) (actual rows=1 loops=40000)

          ->  Append  (cost=0.00..0.24 rows=11 width=8) (actual rows=4 loops=40000)

                ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.17 rows=10 width=8) (actual rows=3 loops=40000)

                      ->  ProjectSet  (cost=0.00..0.07 rows=10 width=4) (actual rows=3 loops=40000)

                            ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=40000)

                ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=8) (actual rows=1 loops=40000)

                      ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual rows=1 loops=40000)

Planning Time: 0.093 ms

Execution Time: 118.922 ms
arr	chk_simpl	chk_michael	chk_bool_and	chk_exist
{1,2,NULL,3}	f	f	f	f
{1,1,1}	f	f	f	f
{2,2,2}	f	f	f	f
{NULL,NULL,NULL}	t	t	t	t
{}	f	t	null	t