---
title: "Changing the unique index behind a PostgreSQL ON CONFLICT upsert | Proofline"
description: "An upsert written as ON CONFLICT (email) needs a unique index on exactly email."
url: https://proofline.sh/learn/postgres-on-conflict-unique-index-change/
---

[Learning center](/learn/)

# Changing the unique index behind a PostgreSQL ON CONFLICT upsert

An upsert written as ON CONFLICT (email) needs a unique index on exactly email. Replace that index with a partial one and every upsert from the previous release fails with there is no unique or exclusion constraint matching the ON CONFLICT specification. Keep both indexes until no release uses the old form, and know that the new behavior begins only when the old index goes.

Schema conflicts The Proofline team November 20, 2026 3 min read

## Let a deleted subscriber sign up again

A newsletter service keeps deleted subscribers for auditing and counts repeat sign-ups with an upsert. The product now wants a deleted address to be able to subscribe again as a new row, so uniqueness should apply only to rows that are not deleted. We ran this on PostgreSQL 16.15.

```
CREATE TABLE subscribers (
    id serial PRIMARY KEY,
    email text NOT NULL,
    deleted_at timestamptz,
    visits integer NOT NULL DEFAULT 1
);
ALTER TABLE subscribers ADD CONSTRAINT subscribers_email_key UNIQUE (email);

-- the previous release's upsert
PREPARE old_upsert(text) AS
  INSERT INTO subscribers (email) VALUES ($1)
  ON CONFLICT (email) DO UPDATE SET visits = subscribers.visits + 1;
EXECUTE old_upsert('ana@example.com');
```

The migration adds a partial unique index and drops the full constraint:

```
CREATE UNIQUE INDEX CONCURRENTLY subscribers_live_email_key
    ON subscribers (email) WHERE deleted_at IS NULL;
ALTER TABLE subscribers DROP CONSTRAINT subscribers_email_key;

EXECUTE old_upsert('ana@example.com');
```

```
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
```

## Why the partial index does not count

PostgreSQL chooses the arbiter index for `ON CONFLICT (email)` by inference. A partial index qualifies only when the statement repeats its predicate, as the [INSERT documentation](https://www.postgresql.org/docs/16/sql-insert.html#SQL-ON-CONFLICT) explains. The previous release's statement has no predicate, so no index qualifies, and every sign-up fails. The new release has to write the predicate:

```
INSERT INTO subscribers (email) VALUES ('ana@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET visits = subscribers.visits + 1;
-- INSERT 0 1
```

An upsert that names the constraint, `ON CONFLICT ON CONSTRAINT subscribers_email_key`, fails as well once the constraint is gone, with `constraint "subscribers_email_key" for table "subscribers" does not exist`.

## While both indexes exist

The compatible order keeps the full constraint while the new release rolls out. We checked what each form does while both indexes exist:

```
-- start again with both the constraint and the partial index
INSERT INTO subscribers (email) VALUES ('ana@example.com')
ON CONFLICT (email) DO UPDATE SET visits = subscribers.visits + 1;           -- old form: works

INSERT INTO subscribers (email) VALUES ('ana@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET visits = subscribers.visits + 1;                               -- new form: works

UPDATE subscribers SET deleted_at = now();

INSERT INTO subscribers (email) VALUES ('ana@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET visits = subscribers.visits + 1;

SELECT email, deleted_at IS NOT NULL AS deleted, visits FROM subscribers;
```

| email | deleted | visits |
| --- | --- | --- |
| [ana@example.com](mailto:ana@example.com) | t | 3 |

Both forms succeed. The re-signup of a deleted address did not create a new row: it updated the deleted one. The full constraint also satisfies the new statement's inference, so it remains an arbiter, and the conflict on it sends the insert into the `DO UPDATE` branch. The feature starts working only after the constraint is dropped. We dropped it and ran the new upsert once more:

| email | deleted | visits |
| --- | --- | --- |
| [ana@example.com](mailto:ana@example.com) | t | 3 |
| [ana@example.com](mailto:ana@example.com) | f | 1 |

Tell whoever tests the feature in staging, or they will report it broken while the deployment is halfway done.

## Release order

1.  Build the partial unique index with `CREATE UNIQUE INDEX CONCURRENTLY`, and check that it is valid. [Retrying a failed CREATE INDEX CONCURRENTLY](/learn/postgres-create-index-concurrently-retry/) shows why.
2.  Deploy the release whose upserts include `WHERE deleted_at IS NULL`.
3.  Once no earlier release is running or kept as a rollback target, drop the full constraint. The new behavior starts here, so this step is the one to announce and to watch.

## What to rehearse

Search the code for every `ON CONFLICT` on the table, including those in other services and batch jobs, and run each against a staging database after step 1 and after step 3. The rehearsal passes when every statement from every release that can still run succeeds, and when a deleted address creates a new row after step 3.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Find the duplicates a PostgreSQL unique constraint will let through](/learn/postgres-unique-constraint-existing-duplicates/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
    
-   ### [Retrying a failed PostgreSQL CREATE INDEX CONCURRENTLY](/learn/postgres-create-index-concurrently-retry/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
