SQLSTATE 01000 WARNING Class 01: Warning

warning collation “…” has version mismatch — 01000

PostgreSQL error “collation … has version mismatch — 01000” (SQLSTATE 01000): what it means, common causes, and how to fix it.

PG 13, 14, 15, 16, 17, 18 Official docs
Last reviewed Jun 2026 Grounded in source

Diagnostic Queries

Symptoms

PostgreSQL detected that a collation’s version differs from the version recorded when dependent indexes were built. It warns (SQLSTATE 01000) that affected indexes may be corrupt and should be rebuilt.

  • The OS/ICU collation library changed versions.
  • Indexes built under the old version may sort differently.
  • A warning prompting REINDEX.

What the server log shows

WARNING:  collation "en_US" has version mismatch
DETAIL:  The collation in the database was created using version 2.28, but the operating system provides version 2.31.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION ... REFRESH VERSION.

Why PostgreSQL raises this — what the manual says

Section 24.2 Collation Support:

“ICU support is required to use this collation, and behavior may change if PostgreSQL is built with a different version of ICU.”

Indexes on text are physically ordered by a collation’s sort rules. If the underlying ICU/libc library is upgraded and its pathkeys/" title="Pathkeys">sort order changes, those indexes may no longer be correctly ordered. PostgreSQL records the version and warns (01000) so you can rebuild before relying on them.

Common causes

  • An OS upgrade changing the libc/ICU collation version.
  • Restoring/migrating onto a host with a different collation library.
  • ICU library upgrade altering sort rules.

How to fix it

  1. Rebuild affected indexes: REINDEX (database or specific indexes).
  2. Then clear the warning: ALTER COLLATION "en_US" REFRESH VERSION; (or per-database).
  3. Standardize collation library versions across hosts in a cluster.

Related & next steps

Reference: PostgreSQL 18 Section 24.2 “Collation Support”.

Was this helpful?