Detecting and Cleaning Duplicates in SQL: Practical Strategies

1. How duplicates happen in practice

The most common cause of duplicates is simply a missing unique constraint on a column that should be unique by business logic, such as a customer's email address or an external reference number. Without that safeguard at the database level, uniqueness relies solely on application logic, which can fail under concurrent requests, faulty retries after a timeout, or simply a forgotten check.

A second common source is repeated or failed batch imports: an import aborts halfway through, gets restarted without prior cleanup, and the rows already inserted in the first half end up duplicated. When merging multiple data sources, for example after a system migration or a company acquisition, a third cause frequently adds to this: the same real world fact was recorded independently in both source systems and is therefore a duplicate, even though both systems each considered it unique.

2. Detecting exact duplicates with GROUP BY and HAVING

For exact duplicates, where the relevant columns match byte for byte, a GROUP BY query followed by HAVING COUNT is the most reliable starting point. It groups all rows by the columns that should be unique by business logic and then filters for groups with more than one entry. The result immediately delivers a list of all affected values along with the count of each duplicate, before a single record is ever changed.

It matters to choose the grouping columns deliberately: too broad a grouping misses relevant duplicates, too narrow a grouping produces false positives for rows that happen to match in a few fields without representing the same business fact. In practice it pays off to start with the narrowest sensible business definition of uniqueness and expand the query incrementally as needed.

SELECT email, COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

3. Finding fuzzy duplicates with similarity functions

Not every duplicate is byte identical. Typos, different spellings, or inconsistent formatting produce records that describe the same business fact but appear different under an exact comparison, for example two company names that differ only by a missing legal entity suffix. These cases need similarity functions instead of exact equality.

PostgreSQL offers trigram based similarity comparisons through the pg_trgm extension, which still deliver high similarity scores even for minor typos. Other systems provide phonetic functions like SOUNDEX, which group similarly sounding names regardless of the exact spelling, as well as, in some cases, native Levenshtein distance functions to measure the minimum number of character edits needed between two values.

-- PostgreSQL: find company names with high trigram similarity
SELECT a.id, a.company_name, b.id, b.company_name,
       similarity(a.company_name, b.company_name) AS score
FROM companies a
JOIN companies b ON a.id < b.id
WHERE similarity(a.company_name, b.company_name) > 0.6
ORDER BY score DESC;

4. ROW_NUMBER() for safely selecting which record to keep

Once duplicates are identified, the harder question follows: which of the several records survives? ROW_NUMBER() as a window function solves this elegantly, assigning a running number within each duplicate group according to a freely chosen criterion, for example the most recent creation date first, or the completeness of the row.

All rows with a number greater than one within their group then count as deletion candidates, while exactly one row per group carries number one and stays. This approach makes the selection logic explicit and traceable, instead of relying on the arbitrary physical order of rows, which can vary depending on the database system and execution plan.

5. Practical example: safe deletion with a CTE and ROW_NUMBER()

In practice, ROW_NUMBER() is usually used inside a common table expression, whose result then serves as the basis for a DELETE or UPDATE. Before any actual deletion, the same query should first be run as a plain SELECT to manually review the deletion candidates before the change becomes final.

This two step approach, check first, then delete, prevents the most common mistake in duplicate cleanup: an incorrectly written PARTITION BY clause that accidentally treats all rows of a table as a single group and thereby deletes almost the entire data set instead of just the actual duplicates.

WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY email
               ORDER BY created_at DESC
           ) AS rn
    FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

6. Cleaning up duplicates with dependent foreign keys

A direct delete fails as soon as other tables reference the duplicate record to be removed via a foreign key, for example orders pointing to a duplicate customer row. A simple DELETE would either fail on a constraint violation or, worse, without proper safeguards, leave orphaned references behind.

The correct sequence is therefore two staged: first, all dependent foreign key references are repointed to the record to be kept, usually via an UPDATE that replaces the old foreign key value with the new one, only then can the actual duplicate record be deleted safely. This sequence must run inside a single transaction, so that no intermediate state with inconsistent references ever becomes visible.

BEGIN;

UPDATE orders
SET customer_id = 4711
WHERE customer_id = 9832;

DELETE FROM customers
WHERE id = 9832;

COMMIT;

7. Prevention: adding unique constraints despite existing duplicates

The best cleanup is of little use if the same root cause immediately creates new duplicates again. After cleanup, a unique constraint should therefore be added on the affected column, which only succeeds if no duplicates actually remain at that point, since the database otherwise rejects the constraint.

In grown systems with very large tables, it is worth planning the constraint rollout in two steps: first a unique index without hard enforcement, to validate in live operation, then the actual constraint conversion at a time of low load, to keep lock times on a production table as short as possible.

8. Batching for large tables: avoiding lock time

A single DELETE that removes millions of rows in one go can lock a production table for the entire duration of the operation and block concurrent writes. Instead of one large statement, an approach in small batches is recommended, for example a few thousand rows per run with a short pause in between, to give other transactions a chance to access the table.

Such batching can be implemented with a LIMIT clause combined with an application level loop, where each run commits in its own short transaction. This approach extends the total duration of the cleanup but significantly reduces the risk of noticeable impact on production operation.

9. Merge strategy: consolidating duplicates into one complete record

Sometimes none of the duplicate records is complete, each instead contains different, individually valid pieces of information, for example one row with the correct phone number and another with the correct address. In this case plain deletion is the wrong strategy, because it loses information that was not fully present in either duplicate.

Instead, a merge is preferable, where the record to be kept is filled field by field with COALESCE from all duplicates, so that every non empty field from a duplicate is carried into the final record, before the remaining duplicates are deleted or repointed as usual. This strategy requires more care in field selection but prevents the silent loss of valuable information.

10. Summary

11. FAQ: Cleaning Duplicates: Key Takeaways