42P22 — 无法确定排序规则

速览

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.

占位符由实际对象、列或参数填充。

版本

锁定目录显示该条件最早见于 PostgreSQL 9.1.0；行为说明固定在 PostgreSQL 18.6 源码，目录存在范围不等于每条消息或功能都从该版本开始。

相关

• 42P21
• 42P16

来源

源码消息、行号和证据边界见 作者证据。

• src/backend/access/hash/hashfunc.c:278-281 (fixed source)
• src/backend/commands/createas.c:198-203 (fixed source)
• src/backend/commands/indexcmds.c:2105-2108 (fixed source)
• src/backend/commands/tablecmds.c:20041-20044 (fixed source)
• src/backend/commands/view.c:76-80 (fixed source)
• src/backend/regex/regc_pg_locale.c:239-242 (fixed source)
• src/backend/utils/adt/like.c:160-163 (fixed source)
• src/backend/utils/adt/like.c:191-194 (fixed source)
• src/backend/utils/adt/varchar.c:735-738 (fixed source)
• src/backend/utils/adt/formatting.c:1654-1658 (fixed source)