Skip to content

42P21 — Collation mismatch

Source-backed reference for PostgreSQL SQLSTATE 42P21.

42P21 — Collation mismatch

At a glance

42P21 (collation_mismatch) Different collation choices meet where PostgreSQL requires one compatible result.

Field Value
SQLSTATE 42P21
Condition collation_mismatch
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_COLLATION_MISMATCH
Aliases —

Meaning

42P21 is raised when a fixed-source path needs one compatible collation but has incompatible choices. Implicit choices are tracked as COLLATE_CONFLICT and may be returned as InvalidOid when the caller permits no common collation; a caller that requires a collation is where the implicit mismatch becomes an ERROR. Explicit COLLATE conflicts fail immediately. The same condition also covers recursive CTE output, inherited or child-column definitions, and foreign-key key-column compatibility.

Diagnosis

For an implicit-collation message, keep both names and inspect whether the caller requested a common collation; differing implicit operands do not all raise 42P21 immediately. For an explicit-collation message, find the two COLLATE clauses and make the intended choice consistent. In a recursive query compare the non-recursive term with the overall column collation. For inheritance or partitioned child errors compare the parent and child column definitions. For a foreign key compare the referencing and referenced key columns: fixed source permits different collations when both are deterministic, but if either is nondeterministic the two collations must be the same. Preserve the DETAIL names rather than treating this as a generic text-cast problem.

Response

Apply one deliberate COLLATE at the expression boundary for an expression conflict, or align the explicit clauses and recursive non-recursive term. Align inherited or child column definitions before rerunning DDL. For a foreign key, choose compatible key collations and satisfy the stricter same-collation rule whenever either side is nondeterministic; then recheck equality and index semantics. If this ERROR occurs inside an explicit transaction, issue ROLLBACK or ROLLBACK TO a pre-error savepoint before the corrected statement; autocommit can retry after returning to idle.

Messages

Representative fixed-source messages include:

  • ERROR message: collation mismatch between implicit collations "%s" and "%s"; HINT: You can choose the collation by applying the COLLATE clause to one or both expressions.
  • ERROR message: collation mismatch between explicit collations "%s" and "%s"
  • ERROR message: recursive query "%s" column %d has collation "%s" in non-recursive term but collation "%s" overall; HINT: Use the COLLATE clause to set the collation of the non-recursive term.
  • ERROR message: column "%s" has a collation conflict; DETAIL: "%s" versus "%s"
  • ERROR message: inherited column "%s" has a collation conflict; DETAIL: "%s" versus "%s"
  • ERROR message: child table "%s" has different collation for column "%s"; DETAIL: "%s" versus "%s"
  • ERROR message: foreign key constraint "%s" cannot be implemented; DETAIL: Key columns "%s" of the referencing table and "%s" of the referenced table have incompatible collations: "%s" and "%s". If either collation is nondeterministic, then both collations have to be the same.

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.