百科 / 错误代码 / Class 42 语法错误或访问规则冲突
42P21 collation_mismatch
排序规则不匹配
ERROR 源码确认 参考 未实测
- 条件名
collation_mismatch- 宏名称
ERRCODE_COLLATION_MISMATCH- 启用版本
- 9.1
- 状态
- 活跃
版本覆盖
速览
42P21（collation_mismatch）需要得到兼容结果时，不同排序规则选择可能发生冲突。
含义
当固定源码路径需要一个兼容的排序规则，却得到不兼容的选择时，会报 42P21。隐式选择会先记录为 COLLATE_CONFLICT；调用方允许没有共同排序规则时可以返回 InvalidOid，只有确实需要排序规则的调用方才把隐式冲突变成 ERROR。显式 COLLATE 冲突则立即失败。同一条件还覆盖递归 CTE 输出、继承或子表列定义，以及外键键列兼容性。
诊断
遇到隐式排序规则消息时保留两个名称，并检查调用方是否要求共同排序规则；不同的隐式操作数不会全部立即抛出 42P21。遇到显式排序规则消息时，找出两个 COLLATE 子句并统一预期选择。递归查询要比较非递归项与整体列排序规则。继承或分区子表错误要比较父表与子表列定义。外键要比较引用表和被引用表的键列：固定源码允许两边都是确定性排序规则时使用不同排序规则；只要任一排序规则不确定，两边就必须相同。保留 DETAIL 中的名称，不要把它笼统当成转成 text 就能解决的问题。
处理
表达式冲突应在表达式边界明确指定一个有意选择的 COLLATE，或统一显式子句及递归非递归项的排序规则。重试 DDL 前先对齐继承列或子表列定义。外键应选择兼容的键列排序规则；只要一侧不确定，就满足两边相同的严格要求，然后重新核对相等比较和索引语义。如果该 ERROR 发生在显式事务中，修正语句前先 ROLLBACK，或回滚到错误前的保存点；自动提交模式下连接回到空闲后再重试。
消息
固定源码中的代表性消息包括：
- 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.
占位符由实际对象、列或参数填充。
报文模板
源码里的格式串，不是某一次运行的输出。%s 之类是占位符，实际报文会填入对象名与取值。适用范围一栏是核验时留下的原始英文记录，未经翻译。
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.
来源：src/backend/parser/parse_collate.c（lines 226-232） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
collation mismatch between explicit collations "%s" and "%s"
来源：src/backend/parser/parse_collate.c（lines 853-858） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
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.
来源：src/backend/parser/parse_cte.c（lines 394-401） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
column "%s" has a collation conflict
"%s" versus "%s"
"%s" versus "%s"
来源：src/backend/commands/tablecmds.c（lines 3297-3303） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
inherited column "%s" has a collation conflict
"%s" versus "%s"
"%s" versus "%s"
来源：src/backend/commands/tablecmds.c（lines 3463-3469） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
child table "%s" has different collation for column "%s"
"%s" versus "%s"
"%s" versus "%s"
来源：src/backend/commands/tablecmds.c（lines 7292-7298） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
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.
来源：src/backend/commands/tablecmds.c（lines 10474-10483） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
版本
锁定目录显示该条件最早见于 PostgreSQL 9.1.0；行为说明固定在 PostgreSQL 18.6 源码，目录存在范围不等于每条消息或功能都从该版本开始。
来源
- 上游源码 src/backend/commands/tablecmds.c 第 10474–10483 行
- 上游源码 src/backend/parser/parse_collate.c 第 853–858 行
- 上游源码 src/backend/commands/tablecmds.c 第 3297–3303 行
- 上游源码 src/backend/commands/tablecmds.c 第 3463–3469 行
- 上游源码 src/backend/parser/parse_collate.c 第 226–232 行
- 上游源码 src/backend/parser/parse_cte.c 第 394–401 行
- 上游源码 src/backend/commands/tablecmds.c 第 7292–7298 行
- 上游源码 src/backend/parser/parse_collate.c 第 208 行
- 上游源码 src/backend/utils/errcodes.txt 第 352 行
- 核验材料 raw/calls/REL_18_6.jsonl
证据
断言
每条断言都写明了是怎么核实的，以及它不覆盖什么。这一层是核验时留下的原始英文记录，照原样呈现，未经翻译。
-
42P21 is the collation_mismatch condition in Class 42.
-
Selected fixed 18.6 paths reject explicit collation conflicts immediately, emit implicit conflicts only when a caller requires one common collation, and check recursive output, inherited or child columns, and foreign-key collation compatibility.
-
select_common_collation(..., none_ok=true) may return InvalidOid while retaining an implicit COLLATE_CONFLICT; a later caller that requires a collation can then report a separate indeterminate-collation condition.
-
Locked catalogue snapshots show this condition by PostgreSQL 9.1.0; source behavior here is fixed at PostgreSQL 18.6.
同类错误代码
Class 42 语法错误或访问规则冲突 下的其他成员。
42000syntax_error_or_access_rule_violation42501insufficient_privilege42601syntax_error42602invalid_name42611invalid_column_definition42622name_too_long42701duplicate_column42702ambiguous_column42703undefined_column42704undefined_object42710duplicate_object42712duplicate_alias42723duplicate_function42725ambiguous_function42803grouping_error42804datatype_mismatch42809wrong_object_type42830invalid_foreign_key42846cannot_coerce42883undefined_function428C9generated_always42939reserved_name42P01undefined_table42P02undefined_parameter42P03duplicate_cursor42P04duplicate_database42P05duplicate_prepared_statement42P06duplicate_schema42P07duplicate_table42P08ambiguous_parameter42P09ambiguous_alias42P10invalid_column_reference42P11invalid_cursor_definition42P12invalid_database_definition42P13invalid_function_definition42P14invalid_prepared_statement_definition42P15invalid_schema_definition42P16invalid_table_definition42P17invalid_object_definition42P18indeterminate_datatype42P19invalid_recursion42P20windowing_error42P22indeterminate_collation