object_not_in_prerequisite_state could not create unique index “…” — 55000

Symptoms

A concurrent unique-index build failed because duplicate values exist, leaving the index invalid. PostgreSQL raises SQLSTATE 55000 (object_not_in_prerequisite_state).

• CREATE UNIQUE INDEX CONCURRENTLY found duplicates.
• The index is left marked INVALID.
• Prerequisite (uniqueness) wasn’t met.

What the server log shows

ERROR:  could not create unique index "idx_users_email"
DETAIL:  Key (email)=(a@example.com) is duplicated.

Why PostgreSQL raises this — what the manual says

As the CREATE INDEX reference (Building Indexes Concurrently) explains:

A concurrent index build left the index in an invalid, unusable state — typically because CREATE INDEX CONCURRENTLY failed partway (for example on a uniqueness violation); drop the invalid index and rebuild it.

A unique index requires the prerequisite that all key values are distinct. When a concurrent build finds duplicates, it cannot finalize the index and leaves it INVALID, reporting 55000.

Common causes

• Duplicate values present when building a unique index.
• A prior failed concurrent build left an invalid index.
• Data not actually unique on the chosen key.

How to fix it

1. Remove/merge duplicate rows, then rebuild the index.
2. Drop the invalid index: DROP INDEX idx_users_email; and recreate after de-duplicating.
3. Verify uniqueness with a GROUP BY … HAVING count(*) > 1 query first.

Diagnostic query

SELECT email, count(*) FROM users GROUP BY email HAVING count(*) > 1;

Lists the duplicate keys blocking the unique index.

Related & next steps

• SQLSTATE 55000 — Object Not In Prerequisite State (full reference)
• Message: materialized view has not been populated
• Glossary: Unique index

Reference: PostgreSQL 18 — CREATE INDEX.