42P21

At a glance

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

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.

Related

• 42P22
• 42P18

Sources

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

• src/backend/parser/parse_collate.c:208-234 (fixed source)
• src/backend/parser/parse_collate.c:804-835 (fixed source)
• src/backend/parser/parse_cte.c:394-401 (fixed source)
• src/backend/commands/tablecmds.c:3297-3303 (fixed source)
• src/backend/commands/tablecmds.c:3463-3469 (fixed source)
• src/backend/commands/tablecmds.c:7292-7298 (fixed source)
• src/backend/commands/tablecmds.c:10474-10483 (fixed source)

Source evidence

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

• src/backend/utils/errcodes.txt · REL_18_6 · line 352
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_collate.c · REL_18_6 · lines 226-232
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_collate.c · REL_18_6 · lines 853-858
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_cte.c · REL_18_6 · lines 394-401
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/commands/tablecmds.c · REL_18_6 · lines 3297-3303
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/commands/tablecmds.c · REL_18_6 · lines 3463-3469
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/commands/tablecmds.c · REL_18_6 · lines 7292-7298
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/commands/tablecmds.c · REL_18_6 · lines 10474-10483
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_collate.c · REL_18_6 · lines 208-234 and 804-835
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• raw/calls/REL_18_6.jsonl · ·

Message templates

collation mismatch between implicit collations "%s" and "%s"

You can choose the collation by applying the COLLATE clause to one or both expressions.

You can choose the collation by applying the COLLATE clause to one or both expressions.

collation mismatch between explicit collations "%s" and "%s"

recursive query "%s" column %d has collation "%s" in non-recursive term but collation "%s" overall

Use the COLLATE clause to set the collation of the non-recursive term.

Use the COLLATE clause to set the collation of the non-recursive term.

column "%s" has a collation conflict

"%s" versus "%s"

"%s" versus "%s"

inherited column "%s" has a collation conflict

"%s" versus "%s"

"%s" versus "%s"

child table "%s" has different collation for column "%s"

"%s" versus "%s"

"%s" versus "%s"

foreign key constraint "%s" cannot be implemented

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.

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.

Reproduction & repair cases

No reproduction case is attached to this condition.

Recorded runtime evidence

No runtime observation is attached to this entry.