42P22 appears when PostgreSQL cannot determine a common collation for a text expression, blocking comparisons or sorting.
PostgreSQL Error 42P22 (indeterminate_collation) occurs when the server cannot pick a single collation for a string expression. Explicitly cast literals or columns to the same collation, or use COLLATE clauses, to resolve the conflict.
PostgreSQL Error 42P22 indeterminate_collation
PostgreSQL raises error code 42P22 (indeterminate_collation) when it cannot decide which collation to apply to a string comparison or ordering operation.
The planner needs a deterministic collation to evaluate operators like =, LIKE, <, or ORDER BY, but the expression involves at least one value whose collation is unknown or conflicting.
The error surfaces most often when you compare a string literal to a column with a non-default collation, join two text columns that use different collations, or run ORDER BY on a union of differently collated text sources.
Fixing it quickly is vital because any statement that triggers 42P22 aborts without returning rows.
The immediate cause is a collation mismatch or absence. If a text literal uses the special pseudo-type unknown, PostgreSQL cannot infer its collation and yields 42P22.
The same happens when two columns in an expression are bound to different collations and you omit an explicit COLLATE clause.
Database upgrades, poorly documented schema changes, or client libraries that hard-code LC_ settings often introduce hidden collation drift. Developers working across locales (en_US vs de_DE) hit the error when collaborating on SQL that mixes their regional settings.
The fastest fix is to assign an explicit collation to every ambiguous text value.
You can cast string literals with ::text COLLATE "collation_name" or convert columns with the COLLATE operator. Aligning all operands to the same database-wide default collation also prevents future mismatches.
When you control the table definition, declare a specific COLLATE on text columns so downstream queries inherit consistent rules.
In ad-hoc statements, wrap comparison operands or ORDER BY targets with COLLATE to override conflicting defaults.
Literal versus column: SELECT 'abc' = name fails if name is citext with en_US.
Use SELECT 'abc'::text COLLATE "en_US" = name.
Union of differently collated columns: CREATE VIEW v AS SELECT a FROM t1 UNION ALL SELECT b FROM t2; Add COLLATE "en_US" to both SELECT parts or cast columns to a shared domain.
Standardize on one collation per database or per schema. Document it in migration scripts.
Enforce it in CI by running sanity queries that compare a known literal to a sentinel row.
Galaxy users can store vetted queries with explicit COLLATE clauses inside Collections. Peers who reuse these queries never trigger 42P22, and Galaxy’s AI copilot will suggest the correct collation when you type ambiguous literals.
Error 42804 (datatype_mismatch) appears when text operands have incompatible types rather than collations.
Cast both sides to text.
Error 42883 (undefined_function) can show up if the collation mismatch hides the right equality operator. Fix 42P22 first, then re-run.
.
The error exists in all supported versions. However, PostgreSQL 12+ improves internal collation handling, reducing false positives.
No. PostgreSQL requires deterministic collations for correct query results. Always supply or align collations instead of disabling checks.
Different operating systems or initdb locales set different default collations. A query that works on en_US may fail on C or de_DE.
Galaxy highlights ambiguous literals, suggests COLLATE clauses via its AI copilot, and lets teams endorse fixed queries so others reuse safe patterns.
.avif)
.avif){kind=logo}
