How to fix Collation mismatch in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Aug 12, 2026· 436 views

PostgreSQL collation mismatch errors occur when database collation settings no longer match the OS locale, typically after system upgrades. Fix by refreshing collation versions and reindexing affected databases.

Quick fix

Fixes Collation mismatch

SELECT datname, datcollate, datctype FROM pg_database;
  1. Check current collation settings across all databases
  2. Refresh collation version for a single database
  3. Refresh collation for all databases at once
  4. Reindex databases to fix corrupted indexes
postgresql — error
SELECT datname, datcollate, datctype FROM pg_database;
Collation mismatch

What this error means

A collation mismatch in PostgreSQL occurs when the collation version stored in the database no longer matches the collation version provided by the operating system. PostgreSQL stores collation settings based on the OS (typically from glibc or ICU libraries) when databases are created. When the OS updates these libraries, the recorded version in the database becomes stale, causing PostgreSQL to detect a version mismatch. This can lead to incorrect string sorting, comparison operations, and potentially corrupted indexes since the database relies on a specific sort order for indexed data. While often just a warning, ignoring it can cause query failures and index corruption over time.

Typical symptoms

  • WARNING: database "<name>" has a collation version mismatch during backup or startup
  • Queries fail with "collation mismatch" errors when comparing or sorting strings
  • Indexes become corrupted or unusable after OS upgrades
  • Error message shows discrepancy like "database version 2.35 but OS provides 2.36"
  • Full-text search or LIKE operators malfunction unexpectedly

Common causes

  1. OS locale library update (glibc or ICU) after PostgreSQL database creation
  2. PostgreSQL server updated but glibc/ICU versions have changed
  3. Database migrated to a system with different collation library versions
  4. Explicit changes to OS locale settings without refreshing PostgreSQL collation
  5. Column-level collation mismatches when joining tables with different collations

How to fix "Collation mismatch"

1Check current collation settings across all databases

Log into PostgreSQL and query which databases have collation mismatches:

sql
SELECT datname, datcollate, datctype FROM pg_database;

This shows each database's collation and character type settings. Note which databases show warnings about version mismatches.

2Refresh collation version for a single database

For each affected database, run the refresh command:

sql
ALTER DATABASE your_database_name REFRESH COLLATION VERSION;

PostgreSQL will display a NOTICE message confirming the version change, e.g., "NOTICE: changing version from 2.35 to 2.36". This updates the stored collation version to match the current OS version.

3Refresh collation for all databases at once

To handle multiple databases efficiently, generate and execute refresh commands for all:

sql
SELECT 'ALTER DATABASE ' || datname || ' REFRESH COLLATION VERSION;'
FROM pg_database
WHERE datname NOT IN ('template0');

Copy the output and execute each ALTER DATABASE command individually.

4Reindex databases to fix corrupted indexes

After refreshing collation, reindex each database to rebuild indexes with the new collation rules:

sql
REINDEX DATABASE your_database_name;

Use the CONCURRENTLY option to avoid locking:

sql
REINDEX DATABASE CONCURRENTLY your_database_name;

This allows other operations to continue while reindexing happens.

5Fix column-level collation mismatches in queries

If you encounter collation mismatch errors when comparing columns with different collations, explicitly specify the collation in your query:

sql
SELECT * FROM users WHERE name COLLATE "en_US.UTF-8" = 'John';

Alternatively, alter the column to use the desired collation:

sql
ALTER TABLE your_table_name
ALTER COLUMN your_column_name
SET DATA TYPE your_data_type COLLATE your_desired_collation;
6Verify collation has been refreshed

Re-run the initial check to confirm collation versions now match:

sql
SELECT datname, datcollate, datctype FROM pg_database;

Console output should no longer show version mismatch warnings. Start a new PostgreSQL session to ensure changes are fully applied.

Advanced notes

For template databases (template1), run ALTER DATABASE template1 REFRESH COLLATION VERSION; to ensure new databases inherit correct collation. If a database remains corrupted after reindexing, consider dumping and restoring it: pg_dump database_name > backup.sql, then drop and recreate. When using ICU collations, similar version tracking applies—glibc updates can trigger mismatches even more frequently. In containerized environments (Docker), collation mismatches commonly occur when upgrading the base image glibc version. Always backup before applying REINDEX CONCURRENTLY on production, and test in a staging environment first.

Frequently asked questions

What does "Collation mismatch" mean?
A collation mismatch in PostgreSQL occurs when the collation version stored in the database no longer matches the collation version provided by the operating system. PostgreSQL stores collation settings based on the OS (typically from glibc or ICU libraries) when databases are created. When the OS updates these libraries, the recorded version in the database becomes stale, causing PostgreSQL to detect a version mismatch.
How do I fix "Collation mismatch"?
PostgreSQL collation mismatch errors occur when database collation settings no longer match the OS locale, typically after system upgrades. Fix by refreshing collation versions and reindexing affected databases.
What causes "Collation mismatch"?
OS locale library update (glibc or ICU) after PostgreSQL database creation PostgreSQL server updated but glibc/ICU versions have changed Database migrated to a system with different collation library versions
Is the "Collation mismatch" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading