How to determine if NULL is contained in an array in Postgres?

Question

How do I determine if NULL is contained in an array in Postgres? Currently using Postgres 9.3.3.

If I test with the following select it returns contains_null = false.

select ARRAY[NULL,1,2,3,4,NULL]::int[] @> ARRAY[NULL]::int[] AS contains_null
select ARRAY[NULL,1,2,3,4,NULL]::int[] @> NULL AS contains_null

I've also tried with:

@> (contains)
<@ (is contained by)
&& (overlap)

More solutions under this similar question: http://stackoverflow.com/questions/34848009/check-if-null-exists-in-postgres-array/34848472 — Erwin Brandstetter, Jan 19 '16 at 13:04
`create table scientist (id integer, firstname varchar(100), lastname varchar(100)); insert into scientist (id, firstname, lastname) values (1, 'albert', 'einstein'); insert into scientist (id, firstname, lastname) values (2, 'isaac', 'newton'); insert into scientist (id, firstname, lastname) values (3, 'marie', null); select * from scientist where lastname is null or lastname in ('einstein', 'newton', null);`. I tried this, but the select query didnt' return the `null` entry here. So, I think, this is how it works, a `null` in the array won't work. — Ashutosh Chamoli, Jun 02 '21 at 14:06

score 16 · Answer 1 · answered Mar 14 '16 at 19:52

One more construction, like @Clodoaldo Neto proposed. Just more compact expression:

CREATE TEMPORARY TABLE null_arrays (
      id serial primary key
    , array_data int[]
);

INSERT INTO null_arrays (array_data)
VALUES
      (ARRAY[1,2, NULL, 4, 5])
    , (ARRAY[1,2, 3, 4, 5])
    , (ARRAY[NULL,2, 3, NULL, 5])
;

SELECT 
    *
FROM 
    null_arrays
WHERE
    TRUE = ANY (SELECT unnest(array_data) IS NULL)
;

Clodoaldo Neto · Accepted Answer · 2017-03-28T08:24:37.100

10

select exists (
    select 1 
    from unnest(array[1, null]) s(a)
    where a is null
);
 exists 
--------
 t

Or shorter:

select bool_or(a is null)
from unnest(array[1, null]) s(a)
;
 bool_or 
---------
 t

edited Mar 28 '17 at 08:24

answered Mar 27 '14 at 18:02

Clodoaldo Neto

118,695
26
233
260

1

is there a better way than using unnest? I've thought about that but with 1+ million records with a column that could have 30+ array items, unnest produces quite a few records. It's an audit table. – thames Mar 27 '14 at 20:10
@thames I think the unnest will perform well and I don't know any other way. – Clodoaldo Neto Mar 27 '14 at 20:54

SONewbiee · Answer 3 · 2017-12-26T22:48:08.603

9

It seems the following works fine in PostgreSQL 10.1.

CREATE TABLE my_table
(
    ...
    my_set  int[] NOT NULL,
    ...
);

SELECT
    my_set
FROM
    my_table
WHERE
    array_position(my_set, NULL) IS NOT NULL;

edited Dec 26 '17 at 22:48

answered Dec 26 '17 at 22:28

SONewbiee

363
2
15

score 7 · Answer 4 · answered Mar 28 '14 at 00:44

Ideally you'd write:

SELECT
    NULL IS NOT DISTINCT FROM ANY ARRAY[NULL,1,2,3,4,NULL]::int[];

but the parser doesn't recognise IS NOT DISTINCT FROM as valid syntax for an operator here, and I can't find an operator alias for it.

You'd have to:

CREATE FUNCTION opr_isnotdistinctfrom(anyelement, anyelement)
RETURNS boolean LANGUAGE SQL IMMUTABLE AS $$
SELECT $1 IS NOT DISTINCT FROM $2; 
$$;

CREATE OPERATOR <<>> (
    PROCEDURE = opr_isnotdistinctfrom,
    LEFTARG = anyelement,
    RIGHTARG = anyelement
);

SELECT NULL <<>> ANY (ARRAY[NULL,1,2,3,4,NULL]::int[]);

which seems a bit gruesome, but should optimize out just fine.

allenwlee · Answer 5 · 2015-02-12T20:56:33.890

4

i didn't want to use unnest either, so i used a comparison of array_length using array_remove to solve a similar problem. Tested on 9.4.1, but should work in 9.3.3.

SELECT
ARRAY_LENGTH(ARRAY[1,null], 1) > ARRAY_LENGTH(ARRAY_REMOVE(ARRAY[1,null], NULL), 1) 
OR ARRAY_LENGTH(ARRAY_REMOVE(ARRAY[1,null], NULL), 1) IS NULL
---------
t

edited Feb 12 '15 at 20:56

answered Feb 11 '15 at 17:08

allenwlee

665
6
21

score 4 · Answer 6 · answered Mar 03 '22 at 16:59

4

SELECT array_position(ARRAY[1,2,3,NULL], NULL)

returns 4 (position of NULL) returns NULL if element not found

answered Mar 03 '22 at 16:59

pankleks

693
6
14

score 1 · Answer 7 · answered Jun 30 '21 at 22:29

Here it is as a reuseable function:

CREATE OR REPLACE FUNCTION f_check_no_null (anyarray)
  RETURNS bool LANGUAGE sql IMMUTABLE AS
 'SELECT CASE WHEN $1 IS NOT NULL THEN array_position($1, NULL) IS NULL END';

You can then use it in a check constraint:

CREATE TABLE foo (
  array_with_no_nulls TEXT[] NOT NULL CHECK(f_check_no_null(array_with_no_nulls))
);

How to determine if NULL is contained in an array in Postgres?

7 Answers7

Linked