How to fix 42P22: indeterminate_collation in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 18, 2025· 115 views

PostgreSQL cannot determine which collation to use when comparing or sorting text values. This happens when multiple columns or expressions with different collations are combined without an explicit COLLATE clause.

Quick fix

Fixes 42P22

-- Before (fails)
SELECT * FROM products ORDER BY name || description;

-- After (works)
SELECT * FROM products ORDER BY name || description COLLATE "en_US";
  1. Add explicit COLLATE clause to ORDER BY
  2. Add COLLATE to comparison operations
  3. Check column collations in your tables
  4. Set consistent collation when creating columns
postgresql — error
-- Before (fails)
SELECT * FROM products ORDER BY name || description;
-- After (works)
SELECT * FROM products ORDER BY name || description COLLATE "en_US";
42P22: indeterminate_collation

What this error means

The 42P22 indeterminate_collation error occurs when PostgreSQL's query planner encounters a string operation (comparison, sorting, or concatenation) involving multiple text values with conflicting collations. PostgreSQL needs to know which collation rules to apply for string operations, but when different columns or expressions have different collations assigned, it cannot automatically choose one. This error commonly appears in ORDER BY clauses, WHERE conditions with string comparisons, joins on text columns, or UNION queries combining results from differently collated sources. PostgreSQL requires an explicit collation specification to resolve the ambiguity.

Typical symptoms

  • Query fails with 'ERROR: could not determine which collation to use for string comparison'
  • ORDER BY on concatenated text columns throws 42P22
  • JOIN conditions on text columns from different tables fail
  • UNION queries combining text columns produce collation errors
  • String comparison with LIKE or pattern matching fails

Common causes

  1. Comparing or sorting columns with different collation settings (e.g., en_US vs fr_FR)
  2. Concatenating text values with mismatched collations in ORDER BY or WHERE
  3. Joining tables where text columns were created with different collations
  4. UNION queries combining text fields from sources with conflicting collations
  5. String literals with 'unknown' pseudo-type being compared to collated columns
  6. Functions or operators combining text expressions without explicit collation

How to fix "42P22: indeterminate_collation"

1Add explicit COLLATE clause to ORDER BY

If your ORDER BY fails with indeterminate collation, specify the collation directly on the sort expression:

sql
-- Before (fails)
SELECT * FROM products ORDER BY name || description;

-- After (works)
SELECT * FROM products ORDER BY name || description COLLATE "en_US";

Choose the appropriate collation for your language and sorting requirements.

2Add COLLATE to comparison operations

For WHERE clauses or JOIN conditions with collation conflicts, apply COLLATE to one or both sides:

sql
-- Comparison in WHERE
SELECT * FROM users
WHERE username COLLATE "C" = 'admin';

-- JOIN condition
SELECT * FROM orders o
JOIN customers c ON o.customer_name COLLATE "en_US" = c.name COLLATE "en_US";

Using "C" collation provides byte-order comparison, which is fast and deterministic.

3Check column collations in your tables

Identify which columns have different collations causing the conflict:

sql
-- View collations for all columns in a table
SELECT column_name, collation_name
FROM information_schema.columns
WHERE table_name = 'your_table'
  AND table_schema = 'public'
  AND collation_name IS NOT NULL;

This helps you understand which columns need explicit COLLATE clauses or schema changes.

4Set consistent collation when creating columns

Prevent future collation conflicts by specifying collation explicitly during table creation:

sql
-- Create table with explicit collation
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT COLLATE "en_US",
  description TEXT COLLATE "en_US"
);

-- Or alter existing column
ALTER TABLE products
ALTER COLUMN name TYPE TEXT COLLATE "en_US";

Note: Altering collation may require rebuilding indexes on that column.

5Use UNION with explicit collation

When combining results from different sources, cast all text columns to the same collation:

sql
-- UNION with collation
SELECT name COLLATE "en_US" AS product_name FROM products
UNION
SELECT title COLLATE "en_US" AS product_name FROM legacy_items;

This ensures the combined result set has a determinate collation.

Advanced notes

Database-level collation: Set a default collation at database creation time using CREATE DATABASE mydb LC_COLLATE 'en_US.UTF-8' to avoid per-column collation issues.

Performance considerations: The "C" collation (byte-order comparison) is fastest but ignores language-specific sorting rules. Use it for case-sensitive identifiers or when performance is critical and language-specific sorting isn't needed.

Collation precedence rules: PostgreSQL follows specific rules when inferring collations. If one expression has an explicit COLLATE clause, it takes precedence. If both have implicit collations, PostgreSQL checks if they match. If they don't match and neither is marked with COLLATE, you get 42P22.

Upgrading considerations: After OS upgrades that change libc versions, you may see collation version mismatches. These are different from indeterminate collation errors but can compound the problem. Run REINDEX DATABASE and ALTER DATABASE REFRESH COLLATION VERSION after major system upgrades.

Common collations: Use "en_US.UTF-8" for English with proper locale-aware sorting, "C" or "POSIX" for byte-order (ASCII) sorting, or language-specific collations like "de_DE", "fr_FR", "ja_JP" for international applications.

Frequently asked questions

What does "42P22" mean?
The 42P22 indeterminate_collation error occurs when PostgreSQL's query planner encounters a string operation (comparison, sorting, or concatenation) involving multiple text values with conflicting collations. PostgreSQL needs to know which collation rules to apply for string operations, but when different columns or expressions have different collations assigned, it cannot automatically choose one. This error commonly appears in ORDER BY clauses, WHERE conditions with string comparisons, joins on text columns, or UNION queries combining results from differently collated sources.
How do I fix "42P22"?
PostgreSQL cannot determine which collation to use when comparing or sorting text values. This happens when multiple columns or expressions with different collations are combined without an explicit COLLATE clause.
What causes "42P22"?
Comparing or sorting columns with different collation settings (e.g., en_US vs fr_FR) Concatenating text values with mismatched collations in ORDER BY or WHERE Joining tables where text columns were created with different collations
Is the "42P22" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading