Often in PostgreSQL you may want to delete multiple rows from a table.
You can use the DELETE statement with the following methods to do so:
Method 1: Delete Multiple Rows Based on Values in List
DELETE FROM athletes
WHERE id IN (2, 5, 8, 9);
This particular example will delete the rows from the athletes table where the value in the id column is equal to 2, 5, 8 or 9.
Method 2: Delete Multiple Rows Based on Values in List
DELETE FROM athletes
WHERE id BETWEEN 4 AND 7;
This particular example will delete the rows from the athletes table where the value in the id column is between 4 and 7, inclusive.
The following example shows how to use each of these methods in practice.
Example: How to Delete Multiple Rows 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,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (1, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (2, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (3, 'Kings', 12, 3);
INSERT INTO athletes VALUES (4, 'Kings', 25, 9);
INSERT INTO athletes VALUES (5, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (6, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (7, 'Hawks', 10, 5);
INSERT INTO athletes VALUES (8, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (9, 'Hawks', 20, 8);
INSERT INTO athletes VALUES (10, 'Hawks', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
id | team | points | assists
----+---------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Hawks | 10 | 5
8 | Celtics | 14 | 13
9 | Hawks | 20 | 8
10 | Hawks | 26 | 3
We can use the following syntax to delete all rows from the table where the value in the id column is equal to 2, 5, 8 or 9:
-- delete rows where id in specific list
DELETE FROM athletes
WHERE id IN (2, 5, 8, 9);
-- view updated rows in table
SELECT * FROM athletes;
id | team | points | assists
----+-------+--------+---------
1 | Mavs | 22 | 5
3 | Kings | 12 | 3
4 | Kings | 25 | 9
6 | Mavs | 16 | 3
7 | Hawks | 10 | 5
10 | Hawks | 26 | 3
When we view the updated table we can see that each of the rows with a value in the id column equal to 2, 5, 8 or 9 has been deleted.
We could also use the following syntax to delete the rows from the table where the value in the id column is between 4 and 7, inclusive:
-- delete rows where id in specific range
DELETE FROM athletes
WHERE id BETWEEN 4 AND 7;
-- view updated rows in table
SELECT * FROM athletes;
id | team | points | assists
----+---------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
8 | Celtics | 14 | 13
9 | Hawks | 20 | 8
10 | Hawks | 26 | 3
When we view the updated table we can see that each of the rows with a value in the id column between 4 and 7 has been deleted.
You can use similar syntax to delete multiple rows from your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Delete Empty Rows in Table
PostgreSQL: How to Delete First N Rows in Table
PostgreSQL: How to Delete All Rows Older than N Days
