# 42P21 — Collation mismatch

> Source-backed reference for PostgreSQL SQLSTATE 42P21.
---

# 42P21 — Collation mismatch

## At a glance {#at-a-glance}

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

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| 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 | `—` |

<!-- source facts: data/errcodes/42P21.json -->
<!-- END SQLSTATE FACTS -->

## Meaning {#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 {#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 {#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 {#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 {#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 {#related}

- [`42P22`](../42p22/)
- [`42P18`](../42p18/)

## Sources {#sources}

Source messages, line anchors, and evidence limits are recorded in [authored evidence](../data/evidence/42p21.json).

- `src/backend/parser/parse_collate.c:208-234` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_collate.c#L208))
- `src/backend/parser/parse_collate.c:804-835` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_collate.c#L804))
- `src/backend/parser/parse_cte.c:394-401` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_cte.c#L394))
- `src/backend/commands/tablecmds.c:3297-3303` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L3297))
- `src/backend/commands/tablecmds.c:3463-3469` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L3463))
- `src/backend/commands/tablecmds.c:7292-7298` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L7292))
- `src/backend/commands/tablecmds.c:10474-10483` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L10474))