Skip to content

42P22 — Indeterminate collation

Source-backed reference for PostgreSQL SQLSTATE 42P22.

42P22 — Indeterminate collation

At a glance

42P22 (indeterminate_collation) An operation that needs one collation has no unambiguous collation to use.

Field Value
SQLSTATE 42P22
Condition indeterminate_collation
Status active
Known present by 9.1.0
Locked snapshots 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3
Macros ERRCODE_INDETERMINATE_COLLATION
Aliases —

Meaning

42P22 is emitted when a selected operation requires a usable collation but derivation produced none, often represented by InvalidOid. This is different from 42P21: the caller-specific message names the operation that cannot proceed instead of presenting two explicit collation names. Fixed core callers include string hashing or comparison, index and partition expressions, CTAS and view output columns, regular expressions, LIKE/ILIKE, and formatting functions.

Diagnosis

Read the operation named by the primary message and trace its string expression to the point where collation should be chosen. Hashing or comparison points to the operator or value expression; an index or partition message points to the definition expression; a CTAS or view-column message points to the output column; regex, LIKE, and ILIKE point to the pattern operands; a %s function message names the formatting function. The fixed callers supply the caller-specific HINT Use the COLLATE clause to set the collation explicitly.. Choose the expression or declared-column boundary deliberately, and distinguish this no-collation requirement from a 42P21 conflict between known choices.

Response

Apply COLLATE at the expression or output-column boundary that owns the semantic choice, then recreate the affected index, partition, view, or CTAS definition when required. Verify comparison, hashing, pattern, regex, and formatting behavior under that collation; do not change the database locale globally just to silence one caller. If this ERROR occurs inside an explicit transaction, issue ROLLBACK or ROLLBACK TO a pre-error savepoint before retrying; autocommit can submit the corrected statement after returning to idle.

Messages

Representative fixed-source messages include:

  • ERROR message: could not determine which collation to use for string hashing; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: no collation was derived for column "%s" with collatable type %s; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: could not determine which collation to use for index expression; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: could not determine which collation to use for partition expression; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: could not determine which collation to use for view column "%s"; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: could not determine which collation to use for regular expression; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: could not determine which collation to use for %s function; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: could not determine which collation to use for LIKE; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: could not determine which collation to use for ILIKE; HINT: Use the COLLATE clause to set the collation explicitly.
  • ERROR message: could not determine which collation to use for string comparison; HINT: Use the COLLATE clause to set the collation explicitly.

Placeholders are filled by the actual object, column, or parameter.

Versions

The locked catalogue shows this condition by PostgreSQL 9.1.0; behavior here is fixed to PostgreSQL 18.6 source. Catalogue presence is a range boundary, not proof that every message or feature began in that release.

Sources

Source messages, line anchors, and evidence limits are recorded in authored evidence.