PostgreSQL: Check if Array Contains NULL Value

Often in PostgreSQL you may want to check if an array field in a table contains a NULL value.

You can use the following syntax to do so:

SELECT *
FROM athletes
WHERE TRUE = ANY(SELECT unnest(points) IS NULL);

This particular example will return only the rows in the athletes table where the array field named points contains NULL as one of the values.

The following example shows how to use this syntax in practice.

Example: Check if Array Field Contains NULL Value in PostgreSQL

Suppose that we create the following table named athletes that contains information about points scored by basketball players during three consecutive seasons:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT,
  points NUMERIC[]
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', '{12.1,15.6,17.8}');
INSERT INTO athletes VALUES (0002, 'Hawks', '{22.5,16.5,18.7}');
INSERT INTO athletes VALUES (0003, 'Rockets', '{20.1,24.4,34.6}');
INSERT INTO athletes VALUES (0004, 'Mavs', '{29.4,30.5,NULL}');
INSERT INTO athletes VALUES (0005, 'Mavs', '{12.6,15.5,17.9}');
INSERT INTO athletes VALUES (0006, 'Celtics', '{20.2,23.4,26.6}');
INSERT INTO athletes VALUES (0007, 'Lakers', '{18.8,NULL,23.5}');

-- view all rows in table
SELECT * FROM athletes;

 id |  team   |      points      
----+---------+------------------
  1 | Mavs    | {12.1,15.6,17.8}
  2 | Hawks   | {22.5,16.5,18.7}
  3 | Rockets | {20.1,24.4,34.6}
  4 | Mavs    | {29.4,30.5,NULL}
  5 | Mavs    | {12.6,15.5,17.9}
  6 | Celtics | {20.2,23.4,26.6}
  7 | Lakers  | {18.8,NULL,23.5}

Suppose that we would like to only select the rows where the array field named points contains NULL as one of the values.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE TRUE = ANY(SELECT unnest(points) IS NULL);

 id |  team  |      points      
----+--------+------------------
  4 | Mavs   | {29.4,30.5,NULL}
  7 | Lakers | {18.8,NULL,23.5}

Notice that the resulting table only contains the two rows where the points field contains NULL as one of the values.

You can use similar syntax to select rows in your own table where a particular array field contains NULL as one of the values.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Check if Array is Empty
PostgreSQL: How to Append Item to Array Column
PostgreSQL: How to Check if Array Field Contains Value