百科 / 错误代码 / Class 42 语法错误或访问规则冲突
42P22 indeterminate_collation
无法确定排序规则
ERROR 源码确认 参考 未实测
- 条件名
indeterminate_collation- 宏名称
ERRCODE_INDETERMINATE_COLLATION- 启用版本
- 9.1
- 状态
- 活跃
版本覆盖
速览
42P22（indeterminate_collation）需要唯一排序规则的操作没有明确可用的排序规则。
含义
当选定操作需要可用的排序规则，但推导结果为空（通常表现为 InvalidOid）时，会报 42P22。这与 42P21 不同：这里的调用方消息指出无法继续的具体操作，而不是列出两个显式排序规则名称。固定核心调用方包括字符串哈希或比较、索引和分区表达式、CTAS 与视图输出列、正则表达式、LIKE/ILIKE 以及格式化函数。
诊断
先读取 primary 消息指出的操作，再追踪字符串表达式到应当选择排序规则的边界。哈希或比较消息指向运算符或值表达式；索引或分区消息指向定义表达式；CTAS 或视图列消息指向输出列；正则、LIKE 和 ILIKE 消息指向模式操作数；%s function 消息会指出格式化函数。固定调用方提供相同的 HINT：Use the COLLATE clause to set the collation explicitly.。应在表达式或声明列的语义边界做明确选择，并把这种缺少排序规则的要求与 42P21 中已知选择之间的冲突区分开。
处理
在真正拥有语义选择的表达式或输出列边界添加 COLLATE，必要时重建受影响的索引、分区、视图或 CTAS 定义。在该排序规则下核对比较、哈希、模式、正则和格式化行为；不要为了消除一个调用方错误而全局修改数据库 locale。如果该 ERROR 发生在显式事务中，重试前先 ROLLBACK，或回滚到错误前的保存点；自动提交模式下，连接返回空闲后即可提交修正语句。
消息
固定源码中的代表性消息包括：
- 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.
占位符由实际对象、列或参数填充。
报文模板
源码里的格式串，不是某一次运行的输出。%s 之类是占位符，实际报文会填入对象名与取值。适用范围一栏是核验时留下的原始英文记录，未经翻译。
could not determine which collation to use for string hashing
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/access/hash/hashfunc.c（lines 278-281） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
no collation was derived for column "%s" with collatable type %s
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/commands/createas.c（lines 198-203） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
could not determine which collation to use for index expression
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/commands/indexcmds.c（lines 2105-2108） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
could not determine which collation to use for partition expression
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/commands/tablecmds.c（lines 20041-20044） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
could not determine which collation to use for view column "%s"
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/commands/view.c（lines 76-80） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
could not determine which collation to use for regular expression
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/regex/regc_pg_locale.c（lines 239-242） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
could not determine which collation to use for %s function
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/utils/adt/formatting.c（lines 1654-1658） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
could not determine which collation to use for LIKE
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/utils/adt/like.c（lines 160-163） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
could not determine which collation to use for ILIKE
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/utils/adt/like.c（lines 191-194） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
could not determine which collation to use for string comparison
Use the COLLATE clause to set the collation explicitly.
Use the COLLATE clause to set the collation explicitly.
来源：src/backend/utils/adt/varchar.c（lines 735-738） @ REL_18_6
适用范围：Placeholders remain dynamic source fields; no concrete runtime value is claimed.
版本
锁定目录显示该条件最早见于 PostgreSQL 9.1.0；行为说明固定在 PostgreSQL 18.6 源码，目录存在范围不等于每条消息或功能都从该版本开始。
来源
- 上游源码 src/backend/utils/adt/varchar.c 第 735–738 行
- 上游源码 src/backend/utils/adt/like.c 第 191–194 行
- 上游源码 src/backend/utils/adt/formatting.c 第 1654–1658 行
- 上游源码 src/backend/commands/indexcmds.c 第 2105–2108 行
- 上游源码 src/backend/access/hash/hashfunc.c 第 278–281 行
- 上游源码 src/backend/regex/regc_pg_locale.c 第 239–242 行
- 上游源码 src/backend/commands/tablecmds.c 第 20041–20044 行
- 上游源码 src/backend/utils/adt/like.c 第 160–163 行
- 上游源码 src/backend/commands/createas.c 第 198–203 行
- 上游源码 src/backend/commands/view.c 第 76–80 行
- 上游源码 src/backend/utils/errcodes.txt 第 353 行
- 核验材料 raw/calls/REL_18_6.jsonl
证据
断言
每条断言都写明了是怎么核实的，以及它不覆盖什么。这一层是核验时留下的原始英文记录，照原样呈现，未经翻译。
-
42P22 is the indeterminate_collation condition in Class 42.
-
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.
-
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.
-
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_error42P21collation_mismatch