# 42P22 — 无法确定排序规则

> PostgreSQL SQLSTATE 42P22：来源与诊断参考。
---

# 42P22 — 无法确定排序规则

## 速览 {#at-a-glance}

`42P22`（**indeterminate_collation**）需要唯一排序规则的操作没有明确可用的排序规则。

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

| 字段 | 值 |
| --- | --- |
| SQLSTATE | `42P22` |
| 条件名 | `indeterminate_collation` |
| 状态 | `有效` |
| 已知存在于 | `9.1.0` |
| 锁定快照 | `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` |
| 宏 | `ERRCODE_INDETERMINATE_COLLATION` |
| 别名 | `—` |

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

## 含义 {#meaning}

当选定操作需要可用的排序规则，但推导结果为空（通常表现为 `InvalidOid`）时，会报 `42P22`。这与 `42P21` 不同：这里的调用方消息指出无法继续的具体操作，而不是列出两个显式排序规则名称。固定核心调用方包括字符串哈希或比较、索引和分区表达式、CTAS 与视图输出列、正则表达式、`LIKE`/`ILIKE` 以及格式化函数。

## 诊断 {#diagnosis}

先读取 primary 消息指出的操作，再追踪字符串表达式到应当选择排序规则的边界。哈希或比较消息指向运算符或值表达式；索引或分区消息指向定义表达式；CTAS 或视图列消息指向输出列；正则、`LIKE` 和 `ILIKE` 消息指向模式操作数；`%s function` 消息会指出格式化函数。固定调用方提供相同的 HINT：`Use the COLLATE clause to set the collation explicitly.`。应在表达式或声明列的语义边界做明确选择，并把这种缺少排序规则的要求与 `42P21` 中已知选择之间的冲突区分开。

## 处理 {#response}

在真正拥有语义选择的表达式或输出列边界添加 `COLLATE`，必要时重建受影响的索引、分区、视图或 CTAS 定义。在该排序规则下核对比较、哈希、模式、正则和格式化行为；不要为了消除一个调用方错误而全局修改数据库 locale。如果该 `ERROR` 发生在显式事务中，重试前先 `ROLLBACK`，或回滚到错误前的保存点；自动提交模式下，连接返回空闲后即可提交修正语句。

## 消息 {#messages}

固定源码中的代表性消息包括：

- **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.`

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

## 版本 {#versions}

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

## 相关 {#related}

- [`42P21`](../42p21/)
- [`42P16`](../42p16/)

## 来源 {#sources}

源码消息、行号和证据边界见 [作者证据](../../data/evidence/42p22.json)。

- `src/backend/access/hash/hashfunc.c:278-281` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/access/hash/hashfunc.c#L278))
- `src/backend/commands/createas.c:198-203` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/createas.c#L198))
- `src/backend/commands/indexcmds.c:2105-2108` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/indexcmds.c#L2105))
- `src/backend/commands/tablecmds.c:20041-20044` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L20041))
- `src/backend/commands/view.c:76-80` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/view.c#L76))
- `src/backend/regex/regc_pg_locale.c:239-242` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/regex/regc_pg_locale.c#L239))
- `src/backend/utils/adt/like.c:160-163` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/like.c#L160))
- `src/backend/utils/adt/like.c:191-194` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/like.c#L191))
- `src/backend/utils/adt/varchar.c:735-738` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/varchar.c#L735))
- `src/backend/utils/adt/formatting.c:1654-1658` ([fixed source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/formatting.c#L1654))