PostgreSQL 42P22 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42P22: indeterminate collation

PostgreSQL error 42P22 occurs when the database engine cannot determine which collation rule to apply to a string expression. This typically happens when two string values with different collations are compared, combined, or passed to a collation-sensitive function without an explicit COLLATE directive.

---

Top 3 Causes

1. Joining or Comparing Columns with Different Collations

When columns from different tables (or even the same table) carry different collation definitions, PostgreSQL cannot automatically resolve which rule wins.

-- Table A uses en_US.UTF-8, Table B uses ko_KR.UTF-8
-- This JOIN raises 42P22
SELECT *
FROM table_a a
JOIN table_b b ON a.username = b.username;

-- Fix: explicitly specify a collation
SELECT *
FROM table_a a
JOIN table_b b ON a.username = b.username COLLATE "en_US.UTF-8";

2. Using Collation-Sensitive Functions on Ambiguous Expressions

Functions like upper(), lower(), and initcap() depend on collation to handle locale-specific character transformations. If the input expression has an indeterminate collation (e.g., the result of concatenating two differently-collated strings), the function cannot proceed.

-- Raises 42P22 when col1 and col2 have different collations
SELECT upper(col1 || col2)
FROM my_table;

-- Fix: pin a single collation on the concatenated result
SELECT upper((col1 || col2) COLLATE "en_US.UTF-8")
FROM my_table;

3. Mixed Collations in CASE or COALESCE Expressions

When branches of a CASE or arguments of COALESCE return strings with differing collations, the resulting expression has no deterministic collation. Errors surface as soon as that result is used in sorting or further string comparisons.

-- Raises 42P22 in ORDER BY
SELECT CASE
    WHEN flag THEN col_a   -- collation A
    ELSE col_b             -- collation B
END AS result
FROM my_table
ORDER BY result;

-- Fix: unify collation in each branch
SELECT CASE
    WHEN flag THEN col_a COLLATE "en_US.UTF-8"
    ELSE col_b COLLATE "en_US.UTF-8"
END AS result
FROM my_table
ORDER BY result;

-- COALESCE fix
SELECT COALESCE(
    col_a COLLATE "en_US.UTF-8",
    col_b COLLATE "en_US.UTF-8"
) AS result
FROM my_table;

---

Quick Fix Solutions

1. Always use explicit COLLATE on any expression that mixes collations.
2. Audit column collations before writing cross-table queries:

SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND collation_name IS NOT NULL
ORDER BY table_name, column_name;

1. Alter mismatched columns to share a single collation:

ALTER TABLE my_table
    ALTER COLUMN name TYPE VARCHAR(200) COLLATE "en_US.UTF-8";

---

Prevention Tips

• Standardize collation at database creation time. Choose one collation (en_US.UTF-8 or C for performance-critical systems) and enforce it across all tables via DDL templates and code review checklists.

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

• Integrate a collation-mismatch check into CI/CD. Run a query against information_schema.columns on every migration to detect columns with non-standard collations before they reach production and cause runtime errors.

---

Related Errors

Code	Name	Relationship
42P21	collation_mismatch	Explicit conflict between two known collations (vs. indeterminate)
42804	datatype_mismatch	Often co-occurs with collation issues in complex expressions
22021	character_not_in_repertoire	Encoding issues common in multilingual environments

---

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