"How to Delete Duplicate Rows in PostgreSQL: Keep One, Drop Rest"
"how to delete duplicate rows in postgres and keep exactly one: the ctid recipe, the ROW_NUMBER recipe, finding dupes first, and adding a UNIQUE constraint after."
Somewhere between a bad CSV import, a retry-happy webhook, and a missing unique constraint, your table ended up with five copies of the same row. Here's how to delete duplicate rows in postgres and keep exactly one: find the duplicates first, pick the copy you want to survive, delete the rest with one of two battle-tested recipes, and then make the database enforce uniqueness so it never happens again. Every query below is runnable — run them in a transaction and verify before you commit. Last updated: October 4, 2026 What you'll learn How to find duplicate rows with GROUP BY ... HAVING COUNT(*) > 1 The ctid recipe: delete every copy except one, no primary key needed The ROW_NUMBER() recipe: keep the latest (or earliest) copy per group What to do on tables with no primary key at all The safe workflow: verify in a transaction before committing Adding a UNIQUE constraint after cleanup so duplicates can't return Step 1 — How to Find Duplicate Rows in Postgres Before Touching Anything Never delete first. Count the damage, grouped by the columns that *should* be unique: -- Which emails appear more than once, and how many copies? SELECT email, COUNT(*) AS copies FROM users GROUP BY email HAVING COUNT(*) > 1 ORDER BY copies DESC; For multi-column duplicates (same name *and* same phone), group by all of them: SELECT first_name, last_name, phone, COUNT(*) AS copies FROM contacts GROUP BY first_name, last_name, phone HAVING COUNT(*) > 1; And get the total blast radius before you start deleting: -- How many rows are excess copies (total rows minus one-per-group)? SELECT COUNT(*) - COUNT(DISTINCT email) AS excess_rows FROM users; Now you know exactly how many rows should disappear. Write that number down — after the delete, the row count should drop by exactly that…