How to fix 42P21: collation_mismatch in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 166 views

PostgreSQL error 42P21 occurs when the database cannot determine which collation to use for string comparison operations. This happens when comparing or joining columns with different collation settings, or when UNION/CASE statements combine strings that use incompatible sorting rules.

Quick fix

Fixes 42P21: collation_mismatch

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');
  1. Identify which columns have mismatched collations
  2. Add explicit COLLATE clause to your query
  3. Alter column collation to match across tables
  4. Refresh collation version after OS or database upgrade
postgresql — error
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');
42P21: collation_mismatch

What this error means

SQLSTATE 42P21 belongs to the SQL standard error class 42 (syntax error or access rule violation) and specifically indicates a "collation mismatch" condition. Collation defines how PostgreSQL compares and sorts text data—including case sensitivity, accent handling, and language-specific ordering rules. When you perform operations like JOIN, UNION, CASE expressions, or string comparisons between columns or expressions that have different collations, PostgreSQL cannot automatically determine which collation to apply. The database raises this error to prevent ambiguous or incorrect results. Unlike a warning, this is a hard error that stops query execution until you explicitly resolve the collation conflict by specifying which collation should be used for the operation.

Typical symptoms

  • Query fails with "ERROR: could not determine which collation to use for string comparison" and SQLSTATE 42P21
  • JOIN operations between tables fail when comparing text columns with different collations
  • UNION queries fail when combining SELECT statements that return text columns with mismatched collations
  • CASE expressions or COALESCE with string values from different sources produce collation mismatch errors
  • Queries that worked on one server fail after migration or OS upgrade due to changed collation versions
  • Index creation fails with collation errors when the table column and index use different collations

Common causes

  1. Joining or comparing columns from different tables where each column was created with a different COLLATE clause
  2. UNION queries combining text columns where one table uses the default database collation and another uses an explicit per-column collation
  3. Database or system collation version changed after an OS upgrade, causing a mismatch between stored collation metadata and current OS locale
  4. Restoring a database backup from a system with different collation settings onto a server with a different locale configuration
  5. String literal comparisons where one operand has an explicit COLLATE clause and the other uses the database default
  6. Mixing columns from databases with different default collations in distributed queries or foreign data wrappers

How to fix "42P21: collation_mismatch"

1Identify which columns have mismatched collations

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

sql
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.

2Add explicit COLLATE clause to your query

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

sql
-- 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.

3Alter column collation to match across tables

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

sql
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.

4Refresh collation version after OS or database upgrade

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:

sql
-- 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:

sql
SELECT 'ALTER DATABASE ' || datname || ' REFRESH COLLATION VERSION;'
FROM pg_database
WHERE datname NOT IN ('template0', 'template1');
5Set a consistent database-wide default collation

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

sql
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.

Advanced notes

Collation mismatches often surface when databases are migrated between different operating systems or Linux distributions where locale packages differ. The "C" collation provides a deterministic, locale-independent sort order based on byte values and is immune to OS changes, making it a safe choice for international applications. However, "C" collation lacks language-specific sorting (e.g., accented characters), so use locale-specific collations like "en_US.utf8" if you need proper alphabetical ordering for human-readable text. Always run REINDEX after ALTER COLLATION REFRESH VERSION because collation changes can affect index sort order. For advanced use cases, you can create custom collations with specific rules using CREATE COLLATION, or use the ICU collation provider (available in PostgreSQL 10+) for more stable cross-platform behavior. When using PostgreSQL extensions like pg_trgm for fuzzy matching, be aware that some index types (GIN, GiST) can be sensitive to collation changes.

Frequently asked questions

What does "42P21: collation_mismatch" mean?
SQLSTATE 42P21 belongs to the SQL standard error class 42 (syntax error or access rule violation) and specifically indicates a "collation mismatch" condition. Collation defines how PostgreSQL compares and sorts text data—including case sensitivity, accent handling, and language-specific ordering rules. When you perform operations like JOIN, UNION, CASE expressions, or string comparisons between columns or expressions that have different collations, PostgreSQL cannot automatically determine which collation to apply.
How do I fix "42P21: collation_mismatch"?
PostgreSQL error 42P21 occurs when the database cannot determine which collation to use for string comparison operations. This happens when comparing or joining columns with different collation settings, or when UNION/CASE statements combine strings that use incompatible sorting rules.
What causes "42P21: collation_mismatch"?
Joining or comparing columns from different tables where each column was created with a different COLLATE clause UNION queries combining text columns where one table uses the default database collation and another uses an explicit per-column collation Database or system collation version changed after an OS upgrade, causing a mismatch between stored collation metadata and current OS locale
Is the "42P21: 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