Postgres 9.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
null
t

-- Performance

-- table with short arrays (40000 rows á 3-4 elements)
CREATE TABLE t1 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;

-- table with long arrays (~ 10000 rows á ~ 100 elements)
CREATE TABLE t2 AS
SELECT array_agg(CASE WHEN random() > 0.1 THEN NULL ELSE (random()* 100000)::int END) AS arr
FROM   generate_series(1, 1000000) i
GROUP  BY (random()* 10000)::int;
40000 rows affected

10001 rows affected

VACUUM ANALYZE t1;
VACUUM ANALYZE t2;
-- prewarm cache (and show counts)
SELECT count(*), count(arr), avg(cardinality(arr)) FROM t1;
SELECT count(*), count(arr), avg(cardinality(arr)) FROM t2;

count
count
avg

40000
40000
3.2500000000000000

count
count
avg

10001
10001
99.9900009999000100

-- xsimple
EXPLAIN (ANALYZE, TIMING OFF)
SELECT -1 = ALL(arr) IS NULL AS xsimple FROM t1;

QUERY PLAN

Seq Scan on t1  (cost=0.00..1234.00 rows=40000 width=34) (actual rows=40000 loops=1)

Planning time: 0.018 ms

Execution time: 6.934 ms

EXPLAIN (ANALYZE, TIMING OFF)
SELECT -1 = ALL(arr) IS NULL AS xsimple FROM t2;

QUERY PLAN

Seq Scan on t2  (cost=0.00..361.02 rows=10001 width=77) (actual rows=10001 loops=1)

Planning time: 0.017 ms

Execution time: 1.896 ms

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

QUERY PLAN

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

Planning time: 0.017 ms

Execution time: 7.778 ms

EXPLAIN (ANALYZE, TIMING OFF)
SELECT 1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL AS simple FROM t2;

QUERY PLAN

Seq Scan on t2  (cost=0.00..486.03 rows=10001 width=77) (actual rows=10001 loops=1)

Planning time: 0.023 ms

Execution time: 2.010 ms

-- array_rem
EXPLAIN (ANALYZE, TIMING OFF)
SELECT array_remove(arr, NULL) = '{}' AS array_rem FROM t1;

QUERY PLAN

Seq Scan on t1  (cost=0.00..934.00 rows=40000 width=34) (actual rows=40000 loops=1)

Planning time: 0.029 ms

Execution time: 10.749 ms

EXPLAIN (ANALYZE, TIMING OFF)
SELECT array_remove(arr, NULL) = '{}' AS array_rem FROM t2;

QUERY PLAN

Seq Scan on t2  (cost=0.00..286.01 rows=10001 width=77) (actual rows=10001 loops=1)

Planning time: 0.017 ms

Execution time: 8.786 ms

-- array_pos
EXPLAIN (ANALYZE, TIMING OFF)
SELECT cardinality(array_positions(arr, NULL)) = cardinality(arr) AS array_pos FROM t1;

QUERY PLAN

Seq Scan on t1  (cost=0.00..1134.00 rows=40000 width=34) (actual rows=40000 loops=1)

Planning time: 0.018 ms

Execution time: 18.284 ms

EXPLAIN (ANALYZE, TIMING OFF)
SELECT cardinality(array_positions(arr, NULL)) = cardinality(arr) AS array_pos FROM t2;

QUERY PLAN

Seq Scan on t2  (cost=0.00..336.02 rows=10001 width=77) (actual rows=10001 loops=1)

Planning time: 0.020 ms

Execution time: 26.493 ms

-- michael
EXPLAIN (ANALYZE, TIMING OFF)
SELECT TRUE = ALL (SELECT unnest(arr) IS NULL) AS michael FROM t1;

QUERY PLAN

Seq Scan on t1  (cost=0.00..15884.00 rows=40000 width=34) (actual rows=40000 loops=1)

  SubPlan 1

    ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual rows=2 loops=40000)

Planning time: 0.035 ms

Execution time: 23.441 ms

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

QUERY PLAN

Seq Scan on t2  (cost=0.00..4023.89 rows=10001 width=77) (actual rows=10001 loops=1)

  SubPlan 1

    ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual rows=10 loops=10001)

Planning time: 0.047 ms

Execution time: 12.715 ms

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

QUERY PLAN

Seq Scan on t1  (cost=0.00..51234.00 rows=40000 width=34) (actual rows=40000 loops=1)

  SubPlan 1

    ->  Aggregate  (cost=1.25..1.26 rows=1 width=4) (actual rows=1 loops=40000)

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

Planning time: 0.042 ms

Execution time: 88.236 ms

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

QUERY PLAN

Seq Scan on t2  (cost=0.00..12862.27 rows=10001 width=77) (actual rows=10001 loops=1)

  SubPlan 1

    ->  Aggregate  (cost=1.25..1.26 rows=1 width=4) (actual rows=1 loops=10001)

          ->  Function Scan on unnest e  (cost=0.00..1.00 rows=100 width=4) (actual rows=100 loops=10001)

Planning time: 0.059 ms

Execution time: 186.554 ms

-- exist
EXPLAIN (ANALYZE, TIMING OFF)
SELECT NOT EXISTS (SELECT unnest(arr) EXCEPT SELECT null) AS exist FROM t1;

QUERY PLAN

Seq Scan on t1  (cost=0.00..71934.00 rows=40000 width=34) (actual rows=40000 loops=1)

  SubPlan 1

    ->  HashSetOp Except  (cost=0.00..1.78 rows=1 width=0) (actual rows=1 loops=40000)

          ->  Append  (cost=0.00..1.53 rows=101 width=0) (actual rows=4 loops=40000)

                ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..1.51 rows=100 width=0) (actual rows=3 loops=40000)

                      ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual rows=3 loops=40000)

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

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

Planning time: 0.066 ms

Execution time: 102.282 ms

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

QUERY PLAN

Seq Scan on t2  (cost=0.00..18037.79 rows=10001 width=77) (actual rows=10001 loops=1)

  SubPlan 1

    ->  HashSetOp Except  (cost=0.00..1.78 rows=1 width=0) (actual rows=1 loops=10001)

          ->  Append  (cost=0.00..1.53 rows=101 width=0) (actual rows=101 loops=10001)

                ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..1.51 rows=100 width=0) (actual rows=100 loops=10001)

                      ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual rows=100 loops=10001)

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

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

Planning time: 0.063 ms

Execution time: 213.559 ms
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	null	t