CODEWITHSUNDEEP
sqlarrayspostgresql
thames
thames

Reputation: 6083

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

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:

  1. @> (contains)
  2. <@ (is contained by)
  3. && (overlap)

Upvotes: 30

Views: 31315

Answers (7)

pankleks
pankleks

Reputation: 733

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

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

Upvotes: 6

Shelvacu
Shelvacu

Reputation: 4380

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))
);

Upvotes: 1

SONewbiee
SONewbiee

Reputation: 363

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;

Upvotes: 9

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125504

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

Upvotes: 11

Nicolai
Nicolai

Reputation: 5787

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)
;

Upvotes: 17

allenwlee
allenwlee

Reputation: 685

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

Upvotes: 5

Craig Ringer
Craig Ringer

Reputation: 325091

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.

Upvotes: 7

Related Questions