↑↓ select ↵ open ⌫ change scope Open full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

SQLSTATE / Class 42 · Syntax Error or Access Rule Violation

42P22

Read the PG 18 manual

indeterminate_collation

Reading PG 18current·documented in 17 of 22 versions, 9.1 to 20

8.18.28.38.49.09.19.29.39.49.59.61011121314151617181920
SQLSTATE
42P22
Condition name
indeterminate_collation
Class
Syntax Error or Access Rule Violation
Source macro
ERRCODE_INDETERMINATE_COLLATION
Evidence
Source path confirmed
Analysis & operational context

English SQLSTATE atlas: authored explanations and source/runtime evidence are separate from the manual definitions. View source

<h1>42P22 — Indeterminate collation</h1>

At a glance

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

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.

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

42P22 is the indeterminate_collation condition in Class 42.

Method: Read fixed REL_18_6 errcodes.txt and locked facts.

Limits: Identity does not enumerate all source branches or client failures.

Source: src/backend/utils/errcodes.txt

Selected fixed 18.6 callers raise 42P22 when a required operation has no determinate collation: string hashing or comparison, index or partition expressions, CTAS or view output columns, regular expressions, LIKE or ILIKE, and formatting functions.

Method: Read selected resolved report groups and the surrounding fixed caller guards, preserving each caller-specific primary and COLLATE hint.

Limits: Representative confirmed core paths only; not exhaustive historical or extension coverage.

Source: src/backend/access/hash/hashfunc.c · Source: src/backend/commands/createas.c · Source: src/backend/commands/indexcmds.c · Source: src/backend/commands/tablecmds.c · Source: src/backend/commands/view.c · Source: src/backend/regex/regc_pg_locale.c · Source: src/backend/utils/adt/formatting.c · Source: src/backend/utils/adt/like.c · Source: src/backend/utils/adt/like.c · Source: src/backend/utils/adt/varchar.c

The fixed callers attach the same explicit-COLLATE hint while naming the operation that lacks a usable collation; the primary message determines which expression or definition boundary to inspect.

Method: Compare the message roles across the selected hash, DDL, regex, pattern, comparison, and formatting report groups.

Limits: Selected message-role comparison only; no runtime execution is claimed.

Source: src/backend/access/hash/hashfunc.c · Source: src/backend/commands/createas.c · Source: src/backend/commands/indexcmds.c · Source: src/backend/commands/tablecmds.c · Source: src/backend/commands/view.c · Source: src/backend/regex/regc_pg_locale.c · Source: src/backend/utils/adt/formatting.c · Source: src/backend/utils/adt/like.c · Source: src/backend/utils/adt/like.c · Source: src/backend/utils/adt/varchar.c

Locked catalogue snapshots show this condition by PostgreSQL 9.1.0; source behavior here is fixed at PostgreSQL 18.6.

Method: Read locked catalogue facts and definition snapshot.

Limits: First observed release is a lower bound, not precise behavioral introduction.

Source: src/backend/utils/errcodes.txt

Message templates

ERROR · message.variant-1

Primary

could not determine which collation to use for string hashing

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/access/hash/hashfunc.c

ERROR · message.variant-2

Primary

no collation was derived for column "%s" with collatable type %s

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/commands/createas.c

ERROR · message.variant-3

Primary

could not determine which collation to use for index expression

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/commands/indexcmds.c

ERROR · message.variant-4

Primary

could not determine which collation to use for partition expression

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/commands/tablecmds.c

ERROR · message.variant-5

Primary

could not determine which collation to use for view column "%s"

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/commands/view.c

ERROR · message.variant-6

Primary

could not determine which collation to use for regular expression

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/regex/regc_pg_locale.c

ERROR · message.variant-7

Primary

could not determine which collation to use for %s function

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/utils/adt/formatting.c

ERROR · message.variant-8

Primary

could not determine which collation to use for LIKE

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/utils/adt/like.c

ERROR · message.variant-9

Primary

could not determine which collation to use for ILIKE

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/utils/adt/like.c

ERROR · message.variant-10

Primary

could not determine which collation to use for string comparison

Hint

Use the COLLATE clause to set the collation explicitly.

Primary

Use the COLLATE clause to set the collation explicitly.

Placeholders remain dynamic source fields; no concrete runtime value is claimed.

Source: src/backend/utils/adt/varchar.c

Reproduction & repair cases

No reproduction case is attached to this condition.

Recorded runtime evidence

No runtime observation is attached to this entry.

Version history 1
  1. 9.0 → 9.1 added

Definition snapshot: english-manuals:52b2e0b9b3b04d5291109d551d8… · English manual source