SQLSTATE 42P22 ERROR Class 42: Syntax Error or Access Rule Violation

indeterminate_collation could not determine which collation to use for string comparison — 42P22

PostgreSQL error "could not determine which collation to use for string comparison" (SQLSTATE 42P22): what it means, common causes, and how to fix it.

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Diagnostic Queries

Symptoms

A string operation combined values with conflicting (or indeterminate) collations and PostgreSQL could not choose one. It raises SQLSTATE 42P22 (indeterminate_collation).

  • Two operands carry different explicit collations.
  • A HINT suggests using an explicit COLLATE clause.
  • Common when comparing columns from different collations.

What the server log shows

ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.

Why PostgreSQL raises this — what the manual says

Section 24.2 Collation Support (Collation Concepts):

“If there are conflicting non-default implicit collations among the input expressions, then the combination is deemed to have indeterminate collation.”

Each string expression has a collation derivation. When an operation combines two explicit, conflicting collations, the result is indeterminate and PostgreSQL refuses to guess, reporting 42P22.

Common causes

  • Comparing/concatenating columns with different explicit collations.
  • Mixing collations in an expression without an override.
  • Functions receiving operands of conflicting collations.

How to fix it

  1. Add an explicit COLLATE to one operand: a = b COLLATE "en_US".
  2. Normalize collations on the columns involved.
  3. Apply the same COLLATE to all string operands in the expression.

Related & next steps

Reference: PostgreSQL 18 Section 24.2 “Collation Support”.

Was this helpful?