PostgreSQL: How to Check if Group Contains Null

Often in PostgreSQL you may want to check if a particular group contains any NULL values.

You can use the following syntax to do so:

SELECT team, 
       SUM(CASE WHEN points IS NULL THEN 1 ELSE 0 END) AS null_count
FROM athletes
GROUP BY team;

This particular example will count the number of null values in the points column of the table named athletes, grouped by each value in the team column.

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

Example: How to Check if Group Contains Null in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Celtics', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', NULL);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', NULL);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);

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

 id |  team   | points 
----+---------+--------
  1 | Celtics |     22
  2 | Hawks   |   null     
  3 | Kings   |     12
  4 | Kings   |     25
  5 | Mavs    |     31
  6 | Mavs    |     16
  7 | Hawks   |     10
  8 | Celtics |   null    
  9 | Hawks   |     20
 10 | Hawks   |     26

Suppose that we would like to check if each team contains a null value in the points column.

We can use the following syntax to do so:

SELECT team, 
       SUM(CASE WHEN points IS NULL THEN 1 ELSE 0 END) AS null_count
FROM athletes
GROUP BY team;

  team   | null_count 
---------+------------
 Kings   |          0
 Mavs    |          0
 Hawks   |          1
 Celtics |          1

From the output we can see the following:

• The Kings do not have any null values in the points column.
• The Mavs do not have any null values in the points column.
• The Hawks do have null values in the points column.
• The Celtics do have null values in the points column.

You can use similar syntax to check if a group contains null values in your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: Check if Array Contains NULL Value
PostgreSQL: Get All Rows with NULL Value in Any Column
PostgreSQL: How to Use ORDER BY with NULL Values Last