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.
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.
If your ORDER BY fails with indeterminate collation, specify the collation directly on the sort expression:
-- 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.
For WHERE clauses or JOIN conditions with collation conflicts, apply COLLATE to one or both sides:
-- 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.
Identify which columns have different collations causing the conflict:
-- 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.
Prevent future collation conflicts by specifying collation explicitly during table creation:
-- 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.
When combining results from different sources, cast all text columns to the same collation:
-- 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.
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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL