How to fix 42P21: collation_mismatch in PostgreSQL

Query the PostgreSQL information schema to see the collation settings for all text columns involved in your failing query:

SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name IN ('your_table_1', 'your_table_2')
  AND data_type IN ('character varying', 'text', 'char');

This shows which columns use explicit collations versus the database default. Columns with NULL collation_name inherit the database default. Look for differences between the columns you're comparing in your query.

The fastest fix is to explicitly specify which collation to use in the problematic comparison, JOIN, or UNION:

-- For JOIN operations
SELECT a.id, a.name, b.description
FROM users a
JOIN user_archive b ON a.name COLLATE "en_US.utf8" = b.name COLLATE "en_US.utf8";

-- For UNION queries
SELECT name COLLATE "C" FROM table1
UNION
SELECT name COLLATE "C" FROM table2;

-- For CASE expressions
SELECT CASE
  WHEN status COLLATE "en_US" = 'active' THEN 'Active'
  ELSE 'Inactive'
END
FROM accounts;

Replace "en_US.utf8" or "C" with your desired collation. Use "C" for simple byte-by-byte comparison (fastest) or a locale-specific collation for language-aware sorting.

For a permanent fix, change the collation of one or more columns so they all use the same setting:

ALTER TABLE your_table_name
ALTER COLUMN your_column_name
TYPE text COLLATE "en_US.utf8";

Warning: Changing a column's collation requires rewriting the table and rebuilding all indexes that depend on that column. On large tables, this operation can take significant time and will hold an exclusive lock. Always test in a staging environment first and plan for downtime.

If the error appeared after an OS upgrade or database migration, the collation version stored in PostgreSQL may no longer match your system's locale library:

-- Check for collation version mismatches
SELECT collname, collversion
FROM pg_collation
WHERE collversion IS NOT NULL
  AND collversion <> pg_collation_actual_version(oid);

-- Refresh collation version for your database
ALTER DATABASE your_database_name REFRESH COLLATION VERSION;

-- Reindex all affected indexes
REINDEX DATABASE your_database_name;

Run REINDEX after refreshing the collation to ensure all indexes use the updated collation rules. For production systems with many databases, generate refresh commands for all of them:

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

When creating new databases, specify a default collation to prevent future mismatches:

CREATE DATABASE my_app
  WITH ENCODING 'UTF8'
  LC_COLLATE = 'en_US.utf8'
  LC_CTYPE = 'en_US.utf8'
  TEMPLATE template0;

For existing databases, you can't change the default collation directly, but you can standardize on explicit COLLATE clauses in table definitions. Alternatively, create a new database with the correct collation and migrate data using pg_dump and pg_restore.