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:
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)
;
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
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;
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.
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
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))
);
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 thenullentry here. So, I think, this is how it works, anullin the array won't work.