Delete duplicate rows but keep the newest
Remove duplicates in PostgreSQL using ROW_NUMBER over a partition, keeping one row per key.
- SQL
- GreenSnippets
- MIT
- Executed with PostgreSQL 17
DELETE FROM users
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY lower(email)
ORDER BY created_at DESC, id DESC
) AS rn
FROM users
) ranked
WHERE rn > 1
);How it works
Number the rows within each duplicate group, newest first, then delete everything except row 1. Run the inner SELECT first to preview exactly what would be deleted, and wrap the delete in a transaction until you are sure.
This permanently removes data. Take a backup first, and add a unique constraint afterwards.
Related snippets
- SQL
Top N rows per group
Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.
- SQL
Soft delete with a partial unique index
Keep deleted rows but still enforce unique emails among active rows using a partial unique index.
- SQL
Walk a tree with a recursive CTE
Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.
- SQL
Pivot rows into columns with FILTER
Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.