How to fix Collation mismatch in PostgreSQL

Log into PostgreSQL and query which databases have collation mismatches:

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.

For each affected database, run the refresh command:

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.

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

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.

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

REINDEX DATABASE your_database_name;

Use the CONCURRENTLY option to avoid locking:

REINDEX DATABASE CONCURRENTLY your_database_name;

This allows other operations to continue while reindexing happens.

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

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

Alternatively, alter the column to use the desired collation:

ALTER TABLE your_table_name
ALTER COLUMN your_column_name
SET DATA TYPE your_data_type COLLATE your_desired_collation;

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

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.