PostgreSQL: How to Count Null Values in Column

Often in PostgreSQL you may want to count the number of null values in a column.

You can use the following syntax to do so:

SELECT COUNT(*)
FROM athletes
WHERE points IS NULL;

This particular example will count the number of null values in the points column of the table named athletes.

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

Example: How to Count Null Values in Column 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 (
  athleteID INTEGER,
  team TEXT,
  points INTEGER
);

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

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

 athleteid |  team   | points 
-----------+---------+--------
         1 | Mavs    |     22
         2 | Hawks   |     30
         3 | Kings   |     12
         4 | Kings   |   null
         5 | Mavs    |   null
         6 | Mavs    |     16
         7 | Hawks   |     10
         8 | Celtics |     14
         9 | Hawks   |   null
        10 | Hawks   |     26

Notice that the points column has several null values.

We can use the following syntax to count the total number of null values in the points column:

SELECT COUNT(*)
FROM athletes
WHERE points IS NULL;

 count 
-------
     3

From the output we can see that there are a total of 3 null values in the points column of this table.

Feel free to use similar syntax to count the number of null values in a column of 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