Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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)
like image 847
thames Avatar asked Mar 27 '14 17:03

thames


People also ask

How do you check if an array contains NULL value?

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 .

How do I check for NULL values in PostgreSQL?

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.

How do I check if an array is empty Postgres?

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.

Can array contains NULL?

If an array was present, it could have strings (including empty strings) or null values.


2 Answers

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)
;
like image 179
Nicolai Avatar answered Oct 22 '22 14:10

Nicolai


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
like image 9
Clodoaldo Neto Avatar answered Oct 22 '22 15:10

Clodoaldo Neto