Often in PostgreSQL you may want to delete empty rows in a table.
You can use the following methods to do so:
Method 1: Delete Rows with Empty Value in Specific Column
DELETE FROM athletes WHERE points IS NULL;
This particular example will delete the rows from the athletes table where the value in the points column is empty.
Method 2: Delete Rows with Empty Value in Any Column
DELETE FROM athletes WHERE NOT (athletes IS NOT NULL);
This particular example will delete the rows from the athletes table where any column is empty.
The following example shows how to use each method in practice.
Example: How to Delete Empty Rows in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball teams:
-- 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, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, NULL, 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', NULL);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', NULL);
INSERT INTO athletes VALUES (0010, 'Heat', 33);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+------+--------
1 | Mavs | 22
2 | Mavs | 30
3 | | 12
4 | Mavs | 9
5 | Mavs | 14
6 | Heat | 16
7 | Heat |
8 | Heat | 18
9 | Heat |
10 | Heat | 33
Suppose that we would like to delete all rows with any empty value in the points column.
We can use the following syntax to do so:
-- delete rows where 'points' column is empty
DELETE FROM athletes WHERE points IS NULL;
-- view updated table
SELECT * FROM athletes;
id | team | points
----+------+--------
1 | Mavs | 22
2 | Mavs | 30
3 | | 12
4 | Mavs | 9
5 | Mavs | 14
6 | Heat | 16
8 | Heat | 18
10 | Heat | 33
The two rows that had empty values in the points column were deleted from the table.
Suppose instead that we would like to delete all rows with any empty value in any column of the table.
We can use the following syntax to do so:
-- delete rows where any column is empty
DELETE FROM athletes WHERE NOT (athletes IS NOT NULL);
-- view updated table
SELECT * FROM athletes;
id | team | points
----+------+--------
1 | Mavs | 22
2 | Mavs | 30
4 | Mavs | 9
5 | Mavs | 14
6 | Heat | 16
8 | Heat | 18
10 | Heat | 33
Notice that all rows that had an empty value in any column were deleted from the table.
Feel free to use similar syntax to delete empty rows from your own table in PostgreSQL.
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 Fill Null Values with Previous Value
PostgreSQL: How to Replace Null Values with String
