PostgreSQL 42P22 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P22: indeterminate collation

PostgreSQL error 42P22 indeterminate collation occurs when the database engine cannot determine which collation (character sorting and comparison rule) to use for a string operation. This typically happens when two or more string expressions with conflicting or unresolvable collations are combined in a comparison, ordering, or grouping operation. The fix almost always involves explicitly specifying a collation using the COLLATE clause.

Top 3 Causes

1. Comparing Columns with Different Collations

When two text columns defined with different collations are compared directly, PostgreSQL cannot determine which rule wins.

-- Problem: columns have different collations
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    eng_name TEXT COLLATE "en_US.UTF-8",
    local_name TEXT COLLATE "ko_KR.UTF-8"
);

-- This will throw 42P22
SELECT * FROM employees
WHERE eng_name = local_name;
-- ERROR: could not determine which collation to use for string comparison

-- Fix: explicitly specify collation
SELECT * FROM employees
WHERE eng_name COLLATE "en_US.UTF-8" = local_name COLLATE "en_US.UTF-8";

2. Collation Conflict in Conditional Expressions

CASE, COALESCE, and similar expressions that return values from branches with different collations produce an indeterminate result.

-- Problem: branches return different collations
SELECT CASE
    WHEN is_local THEN local_name   -- COLLATE "ko_KR.UTF-8"
    ELSE eng_name                   -- COLLATE "en_US.UTF-8"
END AS display_name
FROM employees
ORDER BY display_name;
-- ERROR: 42P22: indeterminate collation

-- Fix: apply a consistent collation to all branches
SELECT CASE
    WHEN is_local THEN local_name COLLATE "en_US.UTF-8"
    ELSE eng_name COLLATE "en_US.UTF-8"
END AS display_name
FROM employees
ORDER BY display_name;

-- COALESCE fix
SELECT COALESCE(
    preferred_name COLLATE "en_US.UTF-8",
    eng_name COLLATE "en_US.UTF-8"
) AS effective_name
FROM employees;

3. Mixing Default and Explicit Collations in Joins

Joining tables where one uses the database default collation and another uses an explicitly defined collation commonly triggers this error.

-- Problem: mixed collations across joined tables
SELECT u.username, o.customer_name
FROM users u                          -- username: default collation
JOIN orders o ON u.username = o.customer_name;  -- customer_name: COLLATE "C"
-- ERROR: 42P22

-- Fix: normalize collation in the JOIN condition
SELECT u.username, o.customer_name
FROM users u
JOIN orders o
  ON u.username COLLATE "en_US.UTF-8" = o.customer_name COLLATE "en_US.UTF-8";

-- Check collations on existing tables
SELECT column_name, collation_name
FROM information_schema.columns
WHERE table_name = 'orders';

Quick Fix Solutions

Inline COLLATE clause — the fastest fix for one-off queries:

-- ORDER BY with explicit collation
SELECT id, username FROM users
ORDER BY username COLLATE "en_US.UTF-8";

-- LIKE with explicit collation
SELECT * FROM users
WHERE username COLLATE "en_US.UTF-8" LIKE 'John%';

-- ALTER column collation permanently
ALTER TABLE orders
ALTER COLUMN customer_name TYPE TEXT COLLATE "en_US.UTF-8";

Prevention Tips

1. Set collation at database creation and enforce it uniformly.
Create the database with an explicit collation and ensure all text columns follow it. Add a CI check using the query below.

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

-- Audit mismatched collations regularly
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND data_type IN ('text', 'character varying')
  AND collation_name IS DISTINCT FROM (
      SELECT datcollate FROM pg_database
      WHERE datname = current_database()
  );

2. Include collation checks in code reviews.
Any SQL involving string comparisons, ORDER BY, GROUP BY, or JOIN on text columns should be reviewed for collation consistency. Make it a hard rule: if columns come from different sources or were created at different times, always validate their collation before writing comparison logic.

Related Errors

42P21 (collation_mismatch) — explicit collation conflict between two expressions
22021 (character_not_in_repertoire) — character not supported by the specified collation
42804 (datatype_mismatch) — often appears alongside collation issues in mixed-type comparisons

📖 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