Job sql-duplicate-records-merged-with-reconciliation-report · revised 11 October 2026
Merge duplicate records in one table and add the constraint that stops new ones
Duplicate rows in one PostgreSQL or MySQL table are merged on a copy, with removed rows archived, a reverse script that is run, a reconciliation report and a non-blocking uniqueness guard.
You might be seeing
- The same real-world thing appears in several rows, each with different related records
- Adding a unique constraint fails because existing rows collide
- Deleting an extra row would orphan or cascade away orders, invoices or history
No passwords, keys, card details or admin invites needed to start.
What usually happened
Rows that describe the same real-world thing exist several times in a table, because nothing enforced uniqueness, a null counted as distinct, an import or a retry inserted twice, or two systems each created a record. Other tables point at each copy, so removing the extras either fails, orphans data or cascades it away. A safe merge needs an agreed rule for which row survives, every reference repointed first, a report that shows nothing was lost and a way back that restores the removed rows themselves, not only a list of their ids.
Who it’s for: An engineering manager or founder whose database holds several rows for the same customer, product or account, and who dares not delete the extras.
Usually starts when: Reports double-count, a unique index cannot be added because duplicates exist, or support finds the same person under two accounts with orders attached to each.
The result: On a restored copy, every duplicate group is merged into one surviving row, every reference in the agreed tables points at it, each removed row is archived in full, a reconciliation report accounts for every row, and a reverse script is run to show the starting state comes back. A unique index, built without blocking writes, prevents the same duplicates from returning. You receive the scripts to run in production.
Check whether this job fits
These questions check that a deterministic rule can decide what is a duplicate. No data is needed.
Checks you can run yourself
Count the groups without changing anything
On a copy, ask your engineer to count how many values of your match key appear more than once, leaving out rows where the key is empty. This only reads.
SELECT count(*) FROM (SELECT lower(email) FROM customers WHERE email IS NOT NULL AND email <> '' GROUP BY lower(email) HAVING count(*) > 1) AS d;Look for: The number of duplicate groups. Send the count and the table size, not the values. Later, the same count on the masked copy must equal the count on production.
What you get
- A duplicate-group report with counts and a masked sample, and the group counts compared between production and the masked copy
- A merge script that runs in small transactions and can be re-run, the archive of removed rows and the log of every reference moved
- A reverse script, and the log of running it on the copy with table checksums before the merge and after the reverse
- A reconciliation report: rows before and after, references repointed per table, rows removed and archived, and a check that nothing is orphaned
- The migration that adds the uniqueness guard in its non-blocking form, and a runbook for your production window with the order of steps and what to do if the guard build fails
Included
- One table of one entity in one PostgreSQL, or MySQL with InnoDB, database, and the tables that reference it
- Agree an exact or deterministic matching rule, such as a normalised email or a reference number, and a survivor rule. Rows where the match key is empty or null never match each other, and the survivor keeps its own values: nothing is copied into it from the removed rows
- Find the duplicate groups, archive each removed row in full, log every reference moved (table, primary key, old key, new key) and repoint references in the dependent tables on a restored copy
- Write and run a reverse script on the copy that restores the archived rows and the old references from the log
- Produce a reconciliation report and add a unique index that stops recurrence, built in the engine's non-blocking form; a rule that covers only some rows is a partial unique index on PostgreSQL and, because MySQL has no partial indexes, a unique index on a generated column that is NULL outside the rule
Not included
- Running the merge against production; you do that after a restore point you have tested
- Fuzzy or judgement-based matching of names or addresses
- Deduplicating across systems such as a CRM or accounting package
- More than one entity table, or more than 20 dependent tables
- Combining field values from the duplicates into the survivor
- Undoing edits made after the merge: the reverse script restores what the merge changed, and reports any row changed since instead of overwriting it
- Records you must keep unchanged; you name them and they are excluded from the rule, and they must not share a match key with another row
How we know it’s done
Agreed with you before work starts. Each check produces evidence you keep.
On the restored copy, no value of the agreed match key appears in more than one row after the merge; the records you excluded sit outside the duplicate groups and are unchanged.
Evidence: The duplicate count query before and after.
Every reference in the agreed dependent tables points at an existing surviving row, with zero orphans.
Evidence: An orphan check per dependent table.
The reconciliation report accounts for every row: surviving, removed and excluded. The archive holds one full row for each removed row, the reference log has one line for each reference moved, and the count of related rows per table is the same before and after.
Evidence: The reconciliation report with the archive and log counts.
After the merge, the reverse script run on the same copy (rows added by the guard-rehearsal insert script removed first, then the guard removed) returns the entity table and every dependent table to checksums equal to those taken before the merge.
Evidence: The checksums before the merge and after the reverse, per table, and the reverse run log.
The guard, built in the non-blocking form while a script keeps inserting rows on the copy, ends as a valid unique index that rejects an insert repeating the match key. A duplicate inserted deliberately during the build is either rejected or makes the build fail, the table is never blocked, and the runbook's recovery step then ends with a valid index.
Evidence: The build log with the insert script running, the rejection from a test insert, and the log of the failed and recovered build.
Sign-off. You review the masked sample, the reconciliation report and the reverse run, confirm the survivor rule was applied, and sign off in writing. Payment follows sign-off; running the script in production stays your decision.
If it fails. If the reconciliation shows rows or references unaccounted for on the copy, or the reverse script does not return the checksums to the state before the merge, you do not pay for this fixed scope and keep the findings. If the duplicates need human judgement, we say so and stop.
When it fits, and when we stop
It fits when
- PostgreSQL, or MySQL with InnoDB, on a version you can name, with the table, its references and a restore point you have tested. We check the guard build against that version's manual at intake; this page was checked against PostgreSQL 18 and MySQL 8.4
- The match key can be expressed as exact values or a deterministic normalisation
- A restored copy can be prepared with personal data replaced consistently, so equal values stay equal under the match rule, and your engineer can run one read-only count of duplicate groups on production and on the copy and send both figures; they must be equal
- Someone with authority over the data agrees the survivor rule in writing
We stop and tell you if
- Most duplicate groups need a person to judge whether they are the same thing
- No restore point has ever been restored successfully
- References to the table are not declared and cannot be listed by you or found by us
- Some records must be kept as they are for a reason you cannot lift, and they sit inside duplicate groups
- The group counts on production and on the masked copy differ, so the copy does not behave like the data
What could go wrong
There are two ways back. The reverse script restores every archived row under its original key and sets each moved reference back from the log, after first removing the guard. We run it on the copy as part of acceptance and compare table checksums with the state before the merge. In production it undoes the merge only: rows changed since the merge are reported and left alone, and rows added since are untouched. Your tested restore point restores the whole database but also loses every write made since it was taken. The guard has a script that removes it.
Scroll the table sideways to read it all.
| Risk | How we handle it |
|---|---|
| Two rows that look the same are different things and are merged. | The rule is exact or deterministic, you approve a masked sample first, and the archive and reference log let any group be put back. |
| A foreign key with a cascading delete removes related history. | References are repointed before any delete, and the reconciliation report counts related rows before and after. |
| The masked copy shows different duplicate groups from production, so the rehearsal proves nothing. | The group counts on production and on the copy are compared before any work; if they differ we stop. |
| Production data changed between the copy and the run, or a new duplicate arrives while the guard is being built. | The runbook fences the writers that create this entity, re-runs the duplicate count immediately before the merge and stops if the groups differ from those reviewed. The guard is built last in the non-blocking form; a build that fails because of a new duplicate fails safely and the runbook says how to recover. |
| Building the uniqueness guard on a large live table blocks writes. | The build uses PostgreSQL's CREATE UNIQUE INDEX CONCURRENTLY, or MySQL's online ALTER TABLE with ALGORITHM=INPLACE and LOCK=NONE, which stops with an error instead of locking if the server cannot honour it. The build is rehearsed on the copy under inserts. |
A second reviewer checks that the survivor rule is applied as written, that no reference is left pointing at a removed row, that the script cannot cascade-delete related data, and that the reverse script was run and not only written. Your authorised person approves the sample and runs the script.
How we deliver
We arrange the work and independent review, then show you the result against the agreed checks. You keep authority over your systems.
- Agree the match rule, the survivor rule, the dependent tables, the records to leave alone and the restored copy in writing
- Compare the duplicate-group count on production (run read-only by your engineer) with the count on the masked copy; stop if they differ. Then find the groups and review a masked sample with you before anything is merged
- Take a checksum of the table and of each dependent table, then write the merge as small, re-runnable transactions: archive each removed row in full, log each reference moved, repoint references, then remove the extras
- Run it on the restored copy and reconcile row counts and references table by table
- On the merged copy, add the uniqueness guard in the non-blocking form while a script keeps inserting rows, show that inserting a duplicate is rejected, and rehearse the recovery step for a build that fails
- Remove the rows the insert script added during the guard rehearsal, write the reverse script, drop the guard built in the previous step, run the reverse on the copy and compare the checksums with those taken before the merge
- Independent review of the scripts and report, then hand over the runbook for your production window
This is a one-off job, not emergency cover or a subscription. We confirm eligibility, the total price, a start window and a delivery date before you accept. Work starts only after agreed inputs, secure access and any permissions are in place. Hosting and database charges are excluded unless the written quote includes them. No charge or booking is created by an enquiry.
Need to keep it working?
Discuss a monthly database health review so new duplicates, orphans and growth are found early.
Ongoing work is separately scoped and quoted: no monitoring, response-time guarantee or automatic subscription is included in this job.
Explore an ongoing engineering lane, or mention the responsibility you need in your enquiry.
What you can check
This is a new service. We have not delivered this job for a client yet.
Other ways to get this done
- PostgreSQL documents that unique constraints treat nulls as distinct by default, with NULLS NOT DISTINCT to change that, and unique partial indexes for rules covering some rows. www.postgresql.org
- PostgreSQL's INSERT ... ON CONFLICT gives an atomic insert-or-skip under concurrency, which prevents new duplicates once a unique index exists. www.postgresql.org
- MySQL's CREATE INDEX manual says a UNIQUE index permits multiple NULL values, and its manual on secondary indexes says an InnoDB unique index can be defined on a virtual generated column. dev.mysql.com
Questions
Can you decide which duplicates are the same person?
Only by a rule you agree. Where a judgement is needed per case, this fixed job does not fit and we say so.
What stops duplicates coming back?
A unique index on the match key, built without blocking writes and tested with an insert that must be rejected. A rule that covers only some rows is a partial unique index on PostgreSQL; MySQL has no partial indexes, so there it is a unique index on a generated column that is NULL outside the rule.
Can the merge be undone?
Yes, on the copy and in the runbook. Each removed row is archived in full, every reference we move is logged, and a reverse script puts both back; we run it and show the table checksums match the state before the merge. It undoes the merge, not edits made afterwards. Your tested restore point reverts everything, including later writes.
Do you touch our production database?
No. We work on a restored copy and hand you a script and a runbook. You run it after your own restore point.
Send an enquiry
Send us
- The engine and version, the table, its approximate row count and what makes two rows the same thing
- How many rows you believe are duplicated, and the tables that point at it, if you know them
- Which row you would keep in a duplicate group: the oldest, the newest, the most complete
- Whether the rule should cover every row or only some, such as only active records
- Do not send credentials, a dump or real rows in the first enquiry
Later, once you agree
- A restored copy of the database with personal data replaced consistently, and the duplicate-group count from production and from the copy
- The schema, including the declared foreign keys, through an authorised company-controlled route
- The written survivor rule and the list of records that must not change
- The date of your tested restore and the person who will run the script
You own the database, the code and every credential. We work on a copy you prepare, such as a staging database restored from a backup with personal data removed or replaced, and we hand work back as a pull request or a script. We never ask for production passwords, and we do not connect to your production database. You apply any change to production yourself, after a restore point that you have tested.
Email fallback: open your mail app
If website submission is unavailable, review and send the fallback email yourself. An email fallback is not a website receipt. Or write to hello@syntheticindustry.ai with “sql-duplicate-records-merged-with-reconciliation-report” as the subject.