Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (num int, ar int[], expected bool);

INSERT INTO t
VALUES ( 1, '{1,2,NULL}'::int[], true) -- standard cases
     , ( 2, '{-1,NULL,2}'      , true)
     , ( 3, '{NULL}'           , true)
     , ( 4, '{1,2,3}'          , false)
     , ( 5, '{-1,2,3}'         , false)
     , ( 6, NULL               , null)
     , (11, '{{1,2,3},{1,2,NULL}}' , true)
     , (12, '{{1,2,3},{-1,2,NULL}}', true)
     , (13, '{{NULL},{NULL}}'      , true)
     , (14, '{{1,2,3},{1,2,3}}'    , false)
     , (15, '{{-1,2,3},{1,2,3}}'   , false)
     ;
11 rows affected

-- for 1-dimensional arrays
SELECT num, ar::text, expected
     , -1 = ANY(ar) IS NULL                                   AS t_simple
     , (SELECT bool_or(x IS NULL) FROM unnest(ar) x)          AS t_safe1
     , EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)    AS t_safe2
     , CASE -1 = ANY(ar)
         WHEN FALSE THEN FALSE
         WHEN TRUE THEN EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)
         ELSE NULLIF(ar IS NOT NULL, FALSE)
       END                                                    AS t_smart1
     , CASE -1 = ANY(ar)
         WHEN FALSE THEN FALSE
         WHEN TRUE THEN EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)
         ELSE TRUE  -- for columns defined NOT NULL
       END                                                    AS t_smart2
     , array_replace(ar, NULL, 0) <> ar                       AS t_93a
     , array_remove(ar, NULL) <> ar                           AS t_93b
     , array_length(array_remove(ar, NULL), 1)
                                       <> array_length(ar, 1) AS t_93c
     , cardinality(array_remove(ar, NULL)) <> cardinality(ar) AS t_94
     , COALESCE(array_position(ar, NULL::int), 0) > 0         AS t_95a
     , array_position(ar, NULL) IS NOT NULL                   AS t_95b
     , CASE WHEN ar IS NOT NULL
            THEN array_position(ar, NULL) IS NOT NULL END     AS t_95c
FROM   t
WHERE  num < 10;


num
ar
expected
t_simple
t_safe1
t_safe2
t_smart1
t_smart2
t_93a
t_93b
t_93c
t_94
t_95a
t_95b
t_95c


1
{1,2,NULL}
t
t
t
t
t
t
t
t
t
t
t
t
t

2
{-1,NULL,2}
t
f
t
t
t
t
t
t
t
t
t
t
t

3
{NULL}
t
t
t
t
t
t
t
t
null
t
t
t
t

4
{1,2,3}
f
f
f
f
f
f
f
f
f
f
f
f
f

5
{-1,2,3}
f
f
f
f
f
f
f
f
f
f
f
f
f

6
null
null
t
null
f
null
t
null
null
null
null
f
f
null


-- for multi-dimensional arrays
SELECT num, ar::text, expected
     , -1 = ANY(ar) IS NULL                                   AS t_simple
     , (SELECT bool_or(x IS NULL) FROM unnest(ar) x)          AS t_safe1
     , EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)    AS t_safe2
     , CASE -1 = ANY(ar)
         WHEN FALSE THEN FALSE
         WHEN TRUE THEN EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)
         ELSE TRUE
       END                                                    AS t_smart
     , array_replace(ar, NULL, 0) <> ar                       AS t_93a
FROM   t;


num
ar
expected
t_simple
t_safe1
t_safe2
t_smart
t_93a


1
{1,2,NULL}
t
t
t
t
t
t

2
{-1,NULL,2}
t
f
t
t
t
t

3
{NULL}
t
t
t
t
t
t

4
{1,2,3}
f
f
f
f
f
f

5
{-1,2,3}
f
f
f
f
f
f

6
null
null
t
null
f
t
null

11
{{1,2,3},{1,2,NULL}}
t
t
t
t
t
t

12
{{1,2,3},{-1,2,NULL}}
t
f
t
t
t
t

13
{{NULL},{NULL}}
t
t
t
t
t
t

14
{{1,2,3},{1,2,3}}
f
f
f
f
f
f

15
{{-1,2,3},{1,2,3}}
f
f
f
f
f
f
num	ar	expected	t_simple	t_safe1	t_safe2	t_smart1	t_smart2	t_93a	t_93b	t_93c	t_94	t_95a	t_95b	t_95c
1	{1,2,NULL}	t	t	t	t	t	t	t	t	t	t	t	t	t
2	{-1,NULL,2}	t	f	t	t	t	t	t	t	t	t	t	t	t
3	{NULL}	t	t	t	t	t	t	t	t	null	t	t	t	t
4	{1,2,3}	f	f	f	f	f	f	f	f	f	f	f	f	f
5	{-1,2,3}	f	f	f	f	f	f	f	f	f	f	f	f	f
6	null	null	t	null	f	null	t	null	null	null	null	f	f	null