How to Find Duplicate Records in Your Database

Someone on your team imported 12,000 contacts from a trade show CSV into the same contacts table that already holds your CRM data. Nobody checked for overlap first. Now you have customers appearing two, three, sometimes four times, and every report that counts contacts is inflated.

This is the most common data quality problem I've seen, and the fix follows a predictable sequence: find the exact duplicates, find the near-duplicates, decide which record to keep, delete the rest, then add constraints so it can't happen again.

The basic duplicate check

Start here. This tells you which emails appear more than once:

SELECT email, COUNT(*) AS cnt
FROM contacts
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

If this returns 4,000 rows, you have 4,000 emails that each appear at least twice. That's your scope.

One gotcha: NULL emails. Two rows with email IS NULL aren't duplicates of each other — they're just incomplete records. The WHERE email IS NOT NULL filter keeps those out. Deal with them separately.

Compound-key duplicates

Single-column matching misses a lot. You'll have contacts with the same name and phone number but different emails (one personal, one work). Or order records where the same order_number + product_id combination appears twice because a retry logic bug inserted the line item a second time.

-- Contacts: same name + phone, different emails
SELECT name, phone, COUNT(*) AS cnt,
       ARRAY_AGG(email ORDER BY created_at) AS emails,
       ARRAY_AGG(id ORDER BY created_at) AS ids
FROM contacts
WHERE name IS NOT NULL AND phone IS NOT NULL
GROUP BY name, phone
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
 
-- Orders: duplicate line items
SELECT order_number, product_id, COUNT(*) AS cnt
FROM order_items
GROUP BY order_number, product_id
HAVING COUNT(*) > 1;

The ARRAY_AGG trick is useful here. It shows you all the email addresses and IDs for each duplicate group in a single row, so you can eyeball whether they're genuinely the same person or a coincidence.

Picking which duplicate to keep

Finding duplicates is step one. Step two is marking which row survives. ROW_NUMBER() partitioned by your duplicate key gives each row in a group a rank, and you keep rank 1.

The usual rule: keep the oldest record (earliest created_at) because it has the most history attached to it, or keep the most recently updated one because it has the freshest data. Pick one. Here's earliest-created:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(email)
           ORDER BY created_at ASC
         ) AS rn
  FROM contacts
  WHERE email IS NOT NULL
)
SELECT id, email, name, phone, source, created_at, rn
FROM ranked
WHERE rn > 1
ORDER BY email, rn;

Everything with rn > 1 is a duplicate you'd delete. Before you do, review the output. Sometimes the second record has a phone number the first one doesn't, or a different source value you want to preserve. If that's the case, you'll need a merge step before deletion — update the surviving record with any non-null fields from the duplicates.

Deleting duplicates with a CTE

Once you've reviewed and you're confident in your ranking logic, the delete pattern is a CTE that identifies the losers:

WITH duplicates AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(email)
           ORDER BY created_at ASC
         ) AS rn
  FROM contacts
  WHERE email IS NOT NULL
)
DELETE FROM contacts
WHERE id IN (
  SELECT id FROM duplicates WHERE rn > 1
);

Run the SELECT version first (the query from the previous section). Count the rows. Then swap SELECT for DELETE. If the row count matches, you're good.

Do this in a transaction. If anything looks wrong, roll back.

Fuzzy duplicate detection

Exact matching catches the obvious cases. But real CRM data has "Jon Smith" and "John Smith" and "Jonathan Smith" who are all the same person. Exact email matching won't catch these if each record has a different email address.

Postgres has two extensions worth knowing about.

pg_trgm (trigram similarity) is the one I reach for first. It breaks strings into three-character chunks and compares overlap. "John" and "Jon" share enough trigrams to score above 0.3 on a 0-to-1 scale.

-- Requires: CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT a.id AS id_a, b.id AS id_b,
       a.name AS name_a, b.name AS name_b,
       a.email AS email_a, b.email AS email_b,
       SIMILARITY(a.name, b.name) AS name_sim
FROM contacts a
JOIN contacts b
  ON a.id < b.id
  AND SIMILARITY(a.name, b.name) > 0.4
  AND SPLIT_PART(a.email, '@', 2) = SPLIT_PART(b.email, '@', 2)
ORDER BY name_sim DESC;

The a.id < b.id condition prevents comparing a row with itself and avoids returning each pair twice. Adding the domain match (SPLIT_PART on @) narrows the search space dramatically. Without it, a self-join on a 100k-row table is 10 billion comparisons and your query will run for hours.

SOUNDEX and LEVENSHTEIN from the fuzzystrmatch extension are the other options. SOUNDEX is fast but coarse — it encodes names into 4-character phonetic codes, so it catches "Smith" / "Smyth" but misses "Jon" / "John" (different SOUNDEX codes). LEVENSHTEIN counts the minimum single-character edits to transform one string into another. LEVENSHTEIN('Jon', 'John') returns 1, which is a strong signal. But LEVENSHTEIN in a self-join is expensive — there's no index acceleration, so it's O(n^2) with a string comparison at each step. Use it on pre-filtered sets, not the full table.

DuckDB handles this differently. There's no pg_trgm extension, but jaro_winkler_similarity() is built in and works well for name matching. If you're doing cross-source dedup in Fastero's DuckDB store, that's the function to use.

Cross-source duplicates

The hardest dedup problem isn't duplicates within a single table. It's the same customer appearing in Stripe as "Acme Corp" with billing@acme.com, and in HubSpot as "Acme Corporation" with jennifer@acme.com. Neither record is wrong. They were created by different teams through different workflows.

This is identity resolution, and it usually needs a tiered approach: exact email match first, then domain match, then fuzzy name match on shared domains. We covered the full pattern in our cross-source deduplication guide, but the short version is that you can't do it with a single GROUP BY. You need both systems in the same query engine.

Fastero's cross-source DuckDB store pulls data from any connected source — Postgres, Stripe, HubSpot, Salesforce, spreadsheets — into a single DuckDB instance where you can run SQL across all of them. That's what makes cross-source dedup practical without building a warehouse first. You write one query that joins stripe_customers against hubspot_contacts on domain, name similarity, or whatever matching logic fits your data.

If you're already running a warehouse, the same queries work there. The point is that cross-source dedup requires both datasets in one place. Getting them there is the actual bottleneck, not the SQL.

Preventing duplicates in the first place

Finding and cleaning duplicates is reactive. The better move is making them structurally impossible.

Unique constraints are the obvious one. If email should be unique, say so:

-- Add a unique constraint (will fail if duplicates already exist)
ALTER TABLE contacts ADD CONSTRAINT contacts_email_unique UNIQUE (email);
 
-- For soft-delete tables: unique only among non-deleted rows
CREATE UNIQUE INDEX contacts_email_active_unique
  ON contacts (email)
  WHERE deleted_at IS NULL;

That partial unique index on the second line is important if you use soft deletes. A regular UNIQUE constraint would prevent you from having a deleted record and a new active record with the same email. The WHERE deleted_at IS NULL filter scopes the uniqueness check to active rows only.

Before adding the constraint, clean existing duplicates first — the ALTER TABLE will fail if violations already exist. Run the detection queries above, resolve the duplicates, then add the constraint.

For CSV imports specifically, stage into a temporary table first, dedup against your production table with a LEFT JOIN ... WHERE existing.id IS NULL, then insert only the new rows. Never INSERT directly from an unvalidated source into a table with business data.

Two other things that help: validate at the application layer before the data hits the database (catch it early), and run the basic GROUP BY duplicate check as a scheduled data quality alert so you know immediately if something slips through.

Try Fastero free — connect your databases, find duplicates across sources with cross-source SQL joins. No credit card required.