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:
To check if all of the values in an array are equal to null , use the every() method to iterate over the array and compare each value to null , e.g. arr. every(value => value === null) . The every method will return true if all values in the array are equal to null .
Example - With SELECT StatementSELECT * FROM employees WHERE first_number IS NULL; This PostgreSQL IS NULL example will return all records from the employees table where the first_name contains a NULL value.
IF array_length(id_clients, 1) > 0 THEN query := query || format(' AND id = ANY(%L))', id_clients); END IF; This excludes both empty array and NULL. Or use cardinality() in Postgres 9.4 or later.
If an array was present, it could have strings (including empty strings) or null values.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With