PostgreSQL 42P21 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P21: Collation Mismatch — Causes, Fixes, and Prevention

PostgreSQL error code 42P21 signals a collation mismatch, which occurs when the database engine encounters two or more string expressions with conflicting collation rules and cannot determine which one to apply. Collations define how strings are sorted, compared, and case-folded, and they can be set at the database, table, column, or even expression level. This error is especially common in multi-environment setups, cross-database joins, and migration workflows.

Top 3 Causes

1. Joining or Comparing Columns with Different Collations

When two columns involved in a JOIN or WHERE clause carry different explicit collations, PostgreSQL cannot resolve which rule governs the comparison.

-- Two tables with columns defined using different collations
CREATE TABLE users (
    username TEXT COLLATE "en_US.UTF-8"
);

CREATE TABLE activity_log (
    username TEXT COLLATE "C"
);

-- This query triggers ERROR 42P21
SELECT u.username, a.action
FROM users u
JOIN activity_log a ON u.username = a.username;
-- ERROR:  42P21: could not determine which collation to use for string hashing

2. Explicit COLLATE Clause Conflicting with Column Definition

Manually appending a COLLATE clause to a literal or expression that clashes with the target column's collation causes the same conflict.

-- Column defined with a specific collation
CREATE TABLE products (
    product_name TEXT COLLATE "ko_KR.UTF-8"
);

-- Querying with a mismatched COLLATE clause triggers 42P21
SELECT * FROM products
WHERE product_name = 'keyboard' COLLATE "C";
-- ERROR:  42P21: collation mismatch between implicit collations "ko_KR.UTF-8" and "C"

3. Cross-Database or Post-Migration Collation Drift

Databases created on different operating systems inherit different default collations. Restoring a pg_dump backup from a Linux server (with en_US.UTF-8) into a macOS instance (defaulting to a different locale) can leave collation metadata inconsistent across restored objects.

-- Check collation settings on the current database
SELECT datname, datcollate, datctype
FROM pg_database
WHERE datname = current_database();

-- Inspect column-level collations across a schema
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND data_type IN ('text', 'character varying', 'character')
ORDER BY table_name, column_name;

Quick Fix Solutions

Fix 1 — Explicitly cast both sides to the same collation:

-- Force both sides to use the same collation
SELECT u.username, a.action
FROM users u
JOIN activity_log a
  ON (u.username COLLATE "C") = (a.username COLLATE "C");

Fix 2 — Alter the column to unify collations at the schema level:

-- Align the collation of both columns (rewrites the table)
ALTER TABLE activity_log
  ALTER COLUMN username TYPE TEXT COLLATE "en_US.UTF-8";

Fix 3 — Use COLLATE "default" to defer to the column's own collation:

-- Let the column's own collation take precedence
SELECT * FROM products
WHERE product_name = 'keyboard' COLLATE "default";

Fix 4 — Create a new database with an explicit, portable collation:

-- Always specify collation parameters at database creation
CREATE DATABASE myapp
  WITH ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE   = 'en_US.UTF-8'
       TEMPLATE   = template0;

Prevention Tips

Use ICU collations for OS-independent portability (PostgreSQL 10+):

ICU-based collations behave consistently regardless of the underlying operating system locale, making them ideal for teams working across Linux, macOS, and Windows environments.

-- Define a project-wide ICU collation
CREATE COLLATION app_standard (
    PROVIDER = icu,
    LOCALE   = 'und-u-ks-level2'  -- language-neutral, case-insensitive
);

-- Apply it uniformly across all text columns
CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,
    customer_name TEXT COLLATE app_standard,
    email         TEXT COLLATE app_standard
);

Enforce collation consistency in code reviews and CI pipelines:

Add a collation audit query to your CI/CD pipeline to catch mismatches before they reach production. Any column returning NULL in collation_name while having a text type should be reviewed and standardized as part of your DDL conventions.

Related Errors

Error Code	Name	Relationship
`42883`	`undefined_function`	Fires when no operator matches due to collation conflicts
`22021`	`character_not_in_repertoire`	Common companion during encoding/collation migrations
`0A000`	`feature_not_supported`	Appears when ICU collations are used on unsupported PostgreSQL versions

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.