PostgreSQL: Get All Rows with NULL Value in Any Column

Often in PostgreSQL you may want to get all rows in a table that have a NULL value in any column.

You can use the following syntax to do so:

SELECT * FROM athletes
WHERE NOT (athletes IS NOT NULL);

This particular example will return all rows from the athletes table that have a NULL value in any column.

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

Example: How to Get All Rows with NULL Value in Any Column in PostgreSQL

Suppose that we create the following table named athletes that contains information about points scored by basketball players on various teams:

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

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

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

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

Notice that both the team and points columns contain NULL values in several rows.

We can use the following syntax to select all rows from this table that have a NULL value in at least one column:

SELECT * FROM athletes
WHERE NOT (athletes IS NOT NULL);

 athleteid | team | points 
-----------+------+--------
         1 | Mavs |       
         4 |      |       
         7 |      |     10

The resulting table contains all rows from the original table that had a NULL value in at least one column.

Feel free to use similar syntax to select all rows with a NULL value in any column of your own table.

Additional Resources

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

PostgreSQL: How to Replace Null Values with Zero
PostgreSQL: How to Fill Null Values with Previous Value
PostgreSQL: How to Select Rows That Do Not Exist in Another Table