Often in PostgreSQL you may want to delete all rows from a table with a date older than a specific number of days.
You can use the following syntax to do so:
DELETE
FROM emp
WHERE start_date < CURRENT_DATE - interval '30 days';
This particular example will delete all rows from the table named emp where the date in the start_date column is older than 30 days.
Note that the CURRENT_DATE command in PostgreSQL returns the current date.
Also note that you can change 30 to any number you’d like to instead delete rows older than a different number of days.
The following example shows how to use this syntax in practice.
Example: Delete All Rows Older than N Days in PostgreSQL
Suppose that we create the following table named emp that contains information about various employees at some company including their starting date and their total sales made:
-- create table named 'emp'
CREATE TABLE emp (
empID INT PRIMARY KEY,
start_date DATE,
sales INT
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '2024-08-01', 134);
INSERT INTO emp VALUES (0002, '2024-07-01', 201);
INSERT INTO emp VALUES (0003, '2024-09-22', 155);
INSERT INTO emp VALUES (0004, '2024-08-26', 98);
INSERT INTO emp VALUES (0005, '2024-08-28', 65);
INSERT INTO emp VALUES (0006, '2024-09-20', 52);
INSERT INTO emp VALUES (0007, '2024-08-22', 50);
-- view all rows in table
SELECT * FROM emp;
empid | start_date | sales
-------+------------+-------
1 | 2024-08-01 | 134
2 | 2024-07-01 | 201
3 | 2024-09-22 | 155
4 | 2024-08-26 | 98
5 | 2024-08-28 | 65
6 | 2024-09-20 | 52
7 | 2024-08-22 | 50
This article is currently being written on 9/26/2024.
Suppose that we would like to delete all rows from this table where the date in the start_date column is older than 30 days.
We can use the following syntax to do so:
-- delete rows with date in start_date column older than 30 days
DELETE
FROM emp
WHERE start_date < CURRENT_DATE - interval '30 days';
-- view updated table
SELECT * FROM emp;
empid | start_date | sales
-------+------------+-------
3 | 2024-09-22 | 155
5 | 2024-08-28 | 65
6 | 2024-09-20 | 52
Notice that all rows with a date in the start_date column older than 30 days have been deleted from the table.
Feel free to use similar syntax to delete all rows older than a specific number of days in your own table.
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 Select Rows in Date Range
PostgreSQL: Select Rows Where Date is Less Than
