Adding a Unique Email Constraint to a Customers Table That Already Has Duplicates

Here’s a scenario almost every product team hits sooner or later. Sign-up has been running for two years. Somebody finally notices that customers.email was never unique, and the login code quietly picks whichever row comes back first. The fix looks like one line of YAML.

unique:
- name: customers_email_key
  columns: [email]

The line is easy. The data is the problem. Two years of sign-ups almost certainly left a few duplicates behind, maybe a user who registered twice, maybe a test account copied from staging. You won’t know until PostgreSQL tries to build the constraint.

What the plan tells you first

Run the planner against the live schema before anything touches the database:

./bin/forgeschema inspect public > current.json
./bin/forgeschema plan --explain current.json desired.yaml

The review lists one add_unique step with its quoted SQL, and it says plainly that building the constraint over existing rows fails if they contain duplicates. It also lists the lock: ACCESS EXCLUSIVE on customers, and nothing else. Your orders and invoices tables keep serving traffic while it runs.

That’s the moment to go looking. A quick GROUP BY email HAVING count(*) > 1 in a read replica tells you how much cleanup you’re in for. ForgeSchema doesn’t do that query for you. It reads catalog metadata, never your rows.

What happens if you skip the cleanup

Say you apply anyway. PostgreSQL starts building the constraint, hits the second [email protected], and stops. ForgeSchema reports the failure with PostgreSQL’s own reason and SQLSTATE 23505, and the whole migration rolls back. Nothing is half applied. No constraint, no partial index, no history row claiming it worked.

The error deliberately leaves out PostgreSQL’s detail line. That line can quote the duplicated key, which is a customer’s email address, and those don’t belong in CI logs.

So you merge or delete the duplicates and apply the same reviewed plan again. It still matches, since nothing was committed and the schema didn’t change. This time it commits. Then confirm it from the outside:

./bin/forgeschema verify --plan reviewed-plan.json

A match means the live schema has the constraint you reviewed. That’s a structural check, it says nothing about whether your app handles the new unique violation gracefully at sign-up. That part is still yours.

The same pattern covers a unique index, or a new unique constraint on any table that already holds data. Plan it, read the warning, clean the data, apply, verify. It’s slower than pasting ALTER TABLE into psql. It’s also the version where a duplicate can’t leave production in a state nobody reviewed.

You can watch a recorded run of the full workflow in the browser demo.