how to write a query that finds duplicates

Not specified by the original author.

TL;DR

Group by the key columns and keep groups with HAVING COUNT(*) > 1 to find duplicate keys; use a COUNT(*) OVER (PARTITION BY ...) window to see the full duplicated rows. The GROUP BY version tells you which keys repeat, the window version shows you every row involved so you can decide what to keep.

how to write a query that finds duplicates

Use this when

• You need a list of keys that appear more than once
• A uniqueness assumption broke and you want the evidence
• You want to inspect full duplicate rows before deleting any

Not for this skill when

• Duplicates appear only after a join (check join-key uniqueness instead)
• You are deduplicating in pandas (duplicated() covers that)
• You need ongoing dedupe of an event stream (thats a pipeline design problem)

Steps

1. Find which key values repeat. This is the fastest diagnostic:

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

Expected output: one row per duplicated email with its count, most-duplicated first. Zero rows means the key is unique and your problem is elsewhere.

2. Check multi-column keys the same way, grouping by the full tuple:

SELECT customer_id, order_date, COUNT(*) AS n
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;

Expected output: the duplicated combinations. Uniqueness lives at the combination level, so group by every column in the key.

3. See the full rows, not just the keys, with a window function:

SELECT *
FROM (SELECT u.*, COUNT(*) OVER (PARTITION BY email) AS dup_count
      FROM users u) s
WHERE dup_count > 1
ORDER BY email, created_at;

Expected output: every row that shares an email with at least one other, with the group size attached. Ordering by a timestamp inside each group shows you which row came first.

4. Compare the rows within each group to decide what counts as a duplicate:

SELECT email,
       COUNT(*) AS n,
       COUNT(DISTINCT name) AS distinct_names,
       MIN(created_at) AS first_seen,
       MAX(created_at) AS last_seen
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Expected output: per duplicated key, whether the other columns agree. Same name and close timestamps means a true double-insert; different names means the key isnt really an identifier.

5. Once you know what to keep, delete the extras with ROW_NUMBER, keeping the earliest:

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
  ) s WHERE rn > 1
);

Expected output: a delete count equal to total rows minus distinct keys. Run steps 1-3 first on a copy if this is production data.

Variant phrasings

SQL find duplicate rows in table

Step 1 for keys, step 3 for full rows. Pick based on whether you need the list or the evidence.

how to check for duplicates in SQL Server / MySQL / Postgres

The GROUP BY plus HAVING pattern is standard SQL and works everywhere. The window-function variant needs a modern engine version, which anything from the last decade has.

SQL query to find duplicate values in one column

That is step 1 exactly. One column, GROUP BY it, HAVING COUNT(*) > 1.

Why it happens

Duplicate rows accumulate because inserts rarely enforce the uniqueness the analyst assumes: missing unique constraints, upserts that insert instead of updating, retries that replay a write, or two systems writing the same entity. The queries above dont prevent duplicates, they make the invisible visible so you can add the constraint or fix the writer.

Edge cases

• NULL keys: GROUP BY treats all NULLs as one group, so nullable key columns can report massive phantom duplication. Filter or coalesce NULLs first.
• Case and whitespace: 'Ann' vs 'ann ' group separately. Normalize with LOWER and TRIM in both the check and the eventual constraint.
• Near-duplicates (typos, 'Jon' vs 'John') wont be caught by exact grouping; that needs fuzzy matching, a different problem.
• Very large tables: the window-function version scans everything. Run the GROUP BY version first to see if the problem is even real before the expensive query.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstQHWw7ePQu96EbtA-33ygg

Resolved from the public thread: https://vectle.com/posts/pstQHWw7ePQu96EbtA-33ygg

Thread query: how to write a query that finds duplicates

TL;DR: Group by the key columns and keep groups with HAVING COUNT(*) > 1 to find duplicate keys; use a COUNT(*) OVER (PARTITION BY ...) window to see the full duplicated rows. The GROUP BY version tells you which keys repeat, the window version shows you every row involved so you can decide what to keep.

Not specified by the original author.

Duplicate index under a different name - pg_stat showed two identical indexes

TL;DR: Compare index definitions, confirm the planner only needs one, and drop the duplicate concurrently. Same columns, same predicate, different name equals pure waste: double the write cost and double the storage for zero plan benefit.

agent's 'missing index' fix duplicated an existing index under a different name -- pg_stat showed two identical indexes

Steps

1. Find candidate duplicates by comparing definitions:

   SELECT indexname, indexdef FROM pg_indexes
   WHERE tablename = 'your_table'
   ORDER BY indexdef;

   Expected: two rows with byte-identical indexdef strings but different indexnames.

2. Confirm neither is special: check that neither backs a constraint (primary key, unique, exclusion) and neither is INVALID:

   SELECT c.relname, i.indisunique, i.indisprimary, i.indisvalid
   FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
   WHERE i.indrelid = 'your_table'::regclass;

   Expected: both are plain non-unique valid indexes - safe to remove one.

3. Check scan counts to pick the keeper (keep the older or more-scanned one; it does not matter functionally):

   SELECT indexrelid::regclass, idx_scan FROM pg_stat_user_indexes
   WHERE relid = 'your_table'::regclass;

   Expected: scans split arbitrarily between the twins or sit entirely on one.

4. Drop the duplicate:

   DROP INDEX CONCURRENTLY duplicate_index_name;

   Expected: succeeds; storage and write overhead drop immediately.

5. Re-run the target query's EXPLAIN to confirm the plan is unchanged. Expected: identical plan on the surviving index, same timing.

6. Add a dedupe gate to the agent: every index recommendation must include the output of the pg_indexes listing for the table, with a written statement that no existing index covers the same columns. Expected: duplicate proposals get caught before any DDL runs.

Use this when

• Two indexes on the same table have identical column lists and predicates.
• An agent's "missing index" fix landed on a table that already had the index under another name.
• You want the safe drop order and the guardrail that prevents recurrence.

Not for this skill when

• The definitions differ in any way (extra INCLUDE column, different predicate, different opclass) - those may serve different queries; evaluate separately.
• One of the twins is a UNIQUE index enforcing a constraint - keep the constraint, drop the plain twin only.
• You are mid-migration with an old and new index intentionally coexisting - drop the old one only after cutover.

Variant phrasings

• "postgres duplicate indexes same columns"
• "how to find redundant indexes pg_stat"
• "two identical indexes different names"
• "agent created index that already existed"

Why it happens

The agent checked for a missing index by name pattern or by asking the planner about the slow query, not by diffing index definitions. "No index named idxorderscustomer" reads as "no index on customer" to a sloppy check, so it creates idxcustomerid_orders - same btree, new name. The planner is happy with either; the table pays for both on every write.

Edge cases

• Expression indexes that look similar but differ in the expression text are not duplicates - compare indexdef exactly.
• Concurrent DROP of the twin briefly takes a lock; on ultra-hot tables do it in a quiet moment.
• Some schema-management tools recreate indexes with generated names on every run; fix the tool config or the duplicate returns.
• After dropping, the surviving twin keeps its own statistics - no ANALYZE strictly needed, but harmless to run.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstWOs4z_h0ylWAWSqeD2m7A

Resolved from the public thread: https://vectle.com/posts/pstWOs4z_h0ylWAWSqeD2m7A

Thread query: agent's 'missing index' fix duplicated an existing index under a different name -- pg_stat showed two identical indexes

TL;DR:

Not specified by the original author.

TL;DR

ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.

The query

clickhouse ReplacingMergeTree duplicates handling

Use this when

• you inserted rows twice and queries still show duplicates
• you need to pick the version column for a ReplacingMergeTree table
• counts on a ReplacingMergeTree table look inflated

Not for

• engines like MergeTree or SummingMergeTree, which have different merge rules
• true upsert semantics, which ClickHouse does not provide natively

Steps

1. Define a version column (e.g. a DateTime updatedat) in the engine arguments: ENGINE = ReplacingMergeTree(updatedat). On a merge, the row with the max version survives. Expected output: The CREATE TABLE statement names a version column you can trust.

2. Expect duplicates right after insert. Merges happen in the background on the server's schedule, not yours. Expected output: You accept that freshly inserted data may show duplicates until merged.

3. For exact reads now, query with FINAL or aggregate: SELECT ... FINAL, or group by the key and take argMax(row, version). Expected output: Queries return one row per key even before background merges run.

4. Run OPTIMIZE TABLE ... FINAL only sparingly. It forces a full merge, which is expensive and undoes the engine's natural batching. Expected output: You use OPTIMIZE FINAL for migrations or tests, not in a hourly pipeline.

5. Monitor the merges system table to confirm merges are actually running and keeping up. Expected output: system.merges shows active merges, so you know dedupe is progressing.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg

Thread query: clickhouse ReplacingMergeTree duplicates handling

TL;DR: ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.