umzzil nng

Posted on Sep 10 Originally published at oraerror.com

PostgreSQL 42P21 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P21: Collation Mismatch

PostgreSQL error 42P21 occurs when the database engine encounters two or more string values with conflicting collation rules and cannot determine which one to apply. This typically surfaces in comparisons (=, <, >), JOIN conditions, UNION queries, or ORDER BY clauses. Understanding and fixing this error requires knowing how PostgreSQL resolves collation precedence.

Top 3 Causes

1. Joining Columns with Different Collations

When two columns from different tables carry different collation definitions, PostgreSQL cannot reconcile them during a JOIN or WHERE comparison.

-- This fails if username uses 'ko_KR.UTF-8' and display_name uses 'en_US.UTF-8'
SELECT a.username, b.display_name
FROM users_kr a
JOIN users_en b ON a.username = b.display_name;
-- ERROR:  42P21: could not determine which collation to use for string comparison

-- Fix: explicitly specify a collation on one side
SELECT a.username, b.display_name
FROM users_kr a
JOIN users_en b ON a.username = b.display_name COLLATE "en_US.UTF-8";

2. Explicit COLLATE Clauses Conflicting in the Same Expression

When a developer manually specifies COLLATE on both sides of an expression with different values, PostgreSQL raises 42P21 immediately.

-- Conflicting explicit collations in one expression
SELECT *
FROM products
WHERE product_name COLLATE "C" = search_term COLLATE "ko_KR.UTF-8";
-- ERROR:  42P21: collation mismatch between explicit collations

-- Fix: use a single consistent collation
SELECT *
FROM products
WHERE product_name COLLATE "C" = search_term COLLATE "C";

3. UNION / UNION ALL with Mismatched Column Collations

When combining result sets with UNION, PostgreSQL must unify the output column types including collation. Mismatched collations between corresponding columns cause this error.

-- store_kr.product_name uses 'ko_KR.UTF-8', store_en uses 'en_US.UTF-8'
SELECT product_name FROM store_kr
UNION ALL
SELECT product_name FROM store_en;
-- ERROR:  42P21: UNION could not determine which collation to use

-- Fix: cast both sides to the same collation
SELECT product_name COLLATE "C" FROM store_kr
UNION ALL
SELECT product_name COLLATE "C" FROM store_en;

Quick Diagnostics

Before fixing, identify which columns are causing the problem:

-- Check collations on all text columns in a table
SELECT column_name, data_type, collation_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'your_table_name';

-- Find columns that differ from the database default collation
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND collation_name IS NOT NULL
  AND collation_name <> (
      SELECT datcollate FROM pg_database WHERE datname = current_database()
  );

Quick Fix Solutions

If you need a permanent fix, alter the column's collation directly:

-- Permanently change a column's collation (rewrites the table)
ALTER TABLE users_kr
  ALTER COLUMN username TYPE VARCHAR(100) COLLATE "en_US.UTF-8";

For a lightweight, query-level fix without schema changes, cast inline:

-- Inline fix using COLLATE in the query
SELECT *
FROM orders
WHERE customer_name COLLATE "C" = 'John Doe' COLLATE "C";

Prevention Tips

1. Standardize collation at database creation time.
Always define your collation strategy before writing a single line of application code. Use template0 to set a custom collation and stick with one standard across all text columns.

CREATE DATABASE myapp
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE   = 'en_US.UTF-8'
    TEMPLATE   = template0;

2. Use ICU collations for multilingual applications (PostgreSQL 10+).
ICU collations provide consistent, locale-aware sorting without OS dependency, reducing the risk of environment-specific collation mismatches across dev, staging, and production.

CREATE TABLE global_products (
    id           SERIAL PRIMARY KEY,
    product_name TEXT COLLATE "und-x-icu"  -- language-neutral ICU collation
);

Related Errors

Error Code	Name	Relation
`42883`	`undefined_function`	Triggered when type/collation mismatch prevents operator resolution
`22021`	`character_not_in_repertoire`	Occurs when a character is unsupported by the assigned collation
`42704`	`undefined_object`	Raised when a non-existent collation name is referenced in `COLLATE`

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

DEV Community