PostgreSQL 排序规则：文本比较、索引与升级风险

操作系统升级后，PostgreSQL 有时会出现这样的告警：

WARNING: database "app" has a collation version mismatch

数据库还能连接，表里的数据也都在。提示中又给出了 REFRESH COLLATION VERSION，看起来执行一下就能处理完。

但这里需要检查的是已经落盘的索引。文本索引按照某套规则排列；如果运行环境换了一套比较规则，旧索引的顺序就可能与当前查询的判断不一致。刷新版本号会让告警消失，却不会重新排列索引中的数据。

Andreas Karlsson 的这份材料从不同语言的排序习惯讲起，最后讨论到升级风险。沿着这条线看，Collation 就不只是建库时顺手选择的一个 locale：它参与 SQL 的比较，也影响数据库如何组织和查找数据。

同一组名字，为什么会排出不同顺序

材料中用了几个欧洲城市的名字。换一种语言习惯，Å、Ä、Ö，甚至开头的 Aa，位置都会发生变化。

下面在测试库中建立三种 ICU 排序规则。后续示例使用 PostgreSQL 18、UTF-8 编码，并要求 PostgreSQL 编译时启用 ICU 支持。

CREATE SCHEMA collation_demo;
CREATE COLLATION collation_demo.de (provider = icu, locale = 'de-DE');
CREATE COLLATION collation_demo.sv (provider = icu, locale = 'sv-SE');
CREATE COLLATION collation_demo.da (provider = icu, locale = 'da-DK');

CREATE TABLE collation_demo.city (name text);
INSERT INTO collation_demo.city VALUES
    ('Aarhus'), ('Essen'), ('Oslo'), ('Valencia'),
    ('Ängelholm'), ('Åre'), ('Öhringen'), ('Östersund');

SELECT name FROM collation_demo.city ORDER BY name COLLATE collation_demo.sv;

将最后一条 SQL 的排序规则分别换成下面几种，得到的顺序是：

这是原材料里的例子，本次在 PostgreSQL 18.4 中也得到了相同结果。德语排序把这些带符号的字母放在相近的基本字母附近；瑞典语把 Å、Ä、Ö 放在字母表末尾；丹麦语中的 Aa 又有自己的排序习惯。

pg_c_utf8 则按 Unicode 码点排序，不尝试模仿任何一种语言的字典。

这也说明了编码与排序规则的区别。UTF-8 决定字符怎样表示成字节；Collation 决定比较这些字符串时采用什么规则。上面所有数据都使用 UTF-8，排序结果仍然可以不同。

排序规则跟着表达式走

数据库有默认排序规则，列可以单独指定，查询里的表达式也可以使用 COLLATE。

CREATE TABLE collation_demo.contact (
    name text COLLATE collation_demo.de
);

SELECT name FROM collation_demo.contact ORDER BY name;
SELECT name FROM collation_demo.contact
ORDER BY name COLLATE collation_demo.sv;

第一条查询使用列上的德语规则，第二条明确使用瑞典语规则。显式写出的 COLLATE 会覆盖来自列的隐式规则。

实际应用里比较容易踩到的问题，是两个列分别带着不同的排序规则。把它们拼接起来可能成功，再对拼接结果排序却报错：拼接本身只负责连接字符串，排序必须选定比较规则。如果 PostgreSQL 无法从输入中确定该用哪一种，就需要在表达式上明确指定。

可以用 pg_collation_for(name) 检查表达式继承的排序规则。跨表查询出现 collation 冲突时，应当先确认业务需要的比较语义，再决定在哪个表达式上添加 COLLATE。

不区分大小写，会影响什么

有些系统希望 Alice 和 alice 被当成同一个用户名。只改变字母在排序结果中的位置，并不能实现这个要求。

PostgreSQL 默认创建的是确定性排序规则，即 deterministic = true。即使 ICU 认为两个字符串处于同一比较等级，PostgreSQL 仍会用字节比较分出差别。因此，要让不同字节的字符串真正比较为相等，需要同时选择合适的 ICU 规则，并关闭这一步字节决胜。

CREATE COLLATION collation_demo.ignore_case (
    provider = icu,
    locale = 'und-u-ks-level2',
    deterministic = false
);

SELECT 'Alice' = 'alice' COLLATE collation_demo.ignore_case AS same_case,
       'e' = 'é' COLLATE collation_demo.ignore_case AS same_accent;

结果分别是 true 和 false。这里的 und 使用 ICU 的根规则，ks-level2 保留重音差别，但不区分大小写。如果改成 ks-level1，重音差别也会被忽略。

“非确定性”这个名字容易让人误以为每次结果不一样。它在这里指的是允许不同字节表示比较为相等，规则本身仍然有明确含义。

这个选择会进入约束判断：

CREATE TABLE collation_demo.account (
    name text COLLATE collation_demo.ignore_case UNIQUE
);
INSERT INTO collation_demo.account VALUES ('Alice');

再插入 alice，会触发唯一约束冲突。本次实验同时验证了 DISTINCT 和 GROUP BY：在这套规则下，Alice、alice、ALICE 会被归为同一组。

因此，是否忽略大小写或重音，需要由字段含义决定。人名检索可以接受较宽松的匹配，账号标识、外部系统传来的编码则可能需要严格区分。不能为了搜索方便，就把整个库的比较规则都改掉。

非确定性 Collation 也有使用限制。PostgreSQL 18 已经支持在这类规则下执行 LIKE，但 ILIKE、SIMILAR TO 和 POSIX 正则表达式仍不支持；旧版本还需要另外核对 LIKE 的支持情况。使用非确定性 Collation 的 B-tree 索引也不能进行 deduplication，索引空间和查询成本需要结合实际数据评估。

ICU 的另一项常用能力是按数值比较字符串里的数字。比如 file2 和 file10，普通字节排序会先放 file10；使用 locale = 'und-u-kn-true'，则会把 file2 放在前面。这类规则适合产品列表、文件名展示，但仍属于字符串比较，并没有把字段转换成数值类型。

查询改了 Collation，索引未必还能提供顺序

在表达式上加 COLLATE 很方便，不过索引是在固定规则下建立的。

CREATE TABLE collation_demo.item (name text COLLATE "C");
INSERT INTO collation_demo.item
SELECT md5(i::text) FROM generate_series(1, 10000) AS i;
CREATE INDEX item_name_idx ON collation_demo.item (name);
ANALYZE collation_demo.item;

EXPLAIN (COSTS OFF)
SELECT name FROM collation_demo.item ORDER BY name LIMIT 20;

EXPLAIN (COSTS OFF)
SELECT name FROM collation_demo.item
ORDER BY name COLLATE collation_demo.de LIMIT 20;

在本次实验中，第一条查询通过 Index Only Scan 按索引顺序取数，没有额外排序。第二条查询出现了 Sort：索引按 C 排列，无法直接提供德语 Collation 要求的顺序。

即使这些测试数据碰巧没有体现出两种规则的排序差异，规划器也不能把两个 Collation 当成同一个。它必须依据索引定义判断能否满足查询，不能依赖当前数据碰巧符合某种顺序。

如果业务经常使用另一种排序规则，可以评估建立匹配的索引：

CREATE INDEX item_name_de_idx
ON collation_demo.item (name COLLATE collation_demo.de);

补上这个索引后，同一条德语排序查询在实验中也去掉了 Sort。生产库是否值得增加索引，还要结合查询频率、写入成本和已有索引判断；这里验证的是排序规则的匹配关系，并非一组性能跑分。

libc、ICU 和 builtin 怎么选

Collation 的 provider 决定规则由谁提供。PostgreSQL 18 中主要有三种选择：

已有系统使用 libc 并不意味着必须马上迁移。更实际的工作是明确当前依赖、记录运行环境，并把排序规则检查纳入系统升级和数据库迁移流程。对于需要语言排序、大小写或重音定制的新字段，ICU 的表达能力更合适。

builtin 从 PostgreSQL 17 开始提供，pg_c_utf8 对应其 C.UTF-8 locale。PostgreSQL 18 又加入了 pg_unicode_fast，对应 PG_UNICODE_FAST。两者都面向 UTF-8、按码点排序，但字符处理能力有差别：

SELECT upper('ß' COLLATE pg_c_utf8) AS simple_mapping,
       upper('ß' COLLATE pg_unicode_fast) AS full_mapping;

在 PostgreSQL 18.4 中，结果是 ß 和 SS。前者使用简单大小写映射，在这个例子中保留原字符；后者使用完整映射，可以把一个字符转换成多个字符。传统 C / POSIX 则按字节排序，大小写转换主要面向 ASCII。

对机器编码、标识符这类不需要自然语言排序的数据，稳定的字节序或码点序通常更容易管理。对人名、地址、商品名称，应先确认用户期望的排序与相等规则。

Andreas 在材料中倾向于用 pg_c_utf8 作为默认选择，把语言相关需求交给 ICU。这个思路适合新系统设计时讨论，但不能直接套用到已有业务。尤其不要把 builtin 理解成“今后所有相关对象都不需要检查”：码点排序本身稳定，Unicode 大小写映射和字符属性仍可能随 PostgreSQL 主版本演进。依赖这些函数的表达式索引，也需要在升级时关注对应变化。

版本告警背后，是旧索引与新规则的冲突

B-tree 能快速查找，是因为它假定索引中的键按当前比较规则有序。规则变化后，这个前提可能不再成立：索引扫描可能漏掉符合条件的记录，排序或唯一性判断也可能出问题。数据页的字节没有损坏，页校验和正常，也不能证明这种逻辑关系仍然正确。

常见触发场景包括操作系统更新、替换容器基础镜像、迁移到使用另一套库版本的机器，以及 pg_upgrade 后的新 PostgreSQL 二进制链接了不同版本的 ICU。物理复制两端也需要保持相容的排序环境，不能只看 PostgreSQL 主版本是否一致。

检查依赖时需要区分 provider：libc 规则主要关注操作系统 locale 实现；ICU 规则关注 ICU 的版本和数据。即使 PostgreSQL 的版本号完全没变，替换这些依赖库仍然可能影响文本比较。

排查时，可以先查看数据库默认规则记录的版本与当前版本：

SELECT datname, datlocprovider,
       datcollversion AS recorded_version,
       pg_database_collation_actual_version(oid) AS actual_version
FROM pg_database
WHERE datallowconn;

再在相关数据库中检查具名 Collation：

SELECT c.oid::regcollation AS collation,
       c.collprovider,
       c.collversion AS recorded_version,
       pg_collation_actual_version(c.oid) AS actual_version
FROM pg_collation AS c
WHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid)
ORDER BY 1;

版本差异是需要调查的信号，不能据此断言每个文本索引都已经出现错误。反过来，没有差异也不是完整的安全证明：某些 provider 不提供版本信息；glibc 版本只是排序数据版本的近似标记，发行版还可能回移补丁。查询中的 NULL 需要结合 provider 理解，不能直接读成“检查通过”。

处理顺序应当是：确定最终使用的运行环境，重建受影响对象并验证，最后刷新版本记录。

对受影响索引，可以安排普通 REINDEX 的维护窗口，或评估 REINDEX CONCURRENTLY。后者能减少对写入的阻塞，但仍有锁等待、额外扫描和空间开销；重建唯一索引时，若新规则让原本不同的值比较为相等，还需要先处理冲突数据。具体限制可结合站内的索引维护继续阅读。

也不要只查一遍 pg_depend 就认为对象已经找全。数据库默认规则的使用、表达式里的字符处理，以及依赖比较结果的分区边界，都需要结合实际定义检查。REINDEX 能重建索引，无法代替这些对象各自的核对和修复。

完成上述工作后，才针对对应对象执行 ALTER COLLATION ... REFRESH VERSION；数据库默认规则则使用 ALTER DATABASE ... REFRESH COLLATION VERSION。这两条命令只更新目录中的版本记录，既不会重建对象，也不会检查修复是否完整。

对于新系统，排序规则应当和字段定义一起确定；对于已有系统，则要把它当成升级依赖来管理。需要语言排序的地方明确使用相应规则，不需要的地方保持简单。这样既能解释查询为什么按这个顺序返回，也能在环境变化前知道哪些对象需要检查。

参考资料

• 原分享：Andreas Karlsson，Everything you need to know about collations，PGConf.DE 2026。
• 排序规则与 ICU 定制：Collation Support、CREATE COLLATION。
• 版本边界：PostgreSQL 17 发布说明、PostgreSQL 18 发布说明、模式匹配。
• 升级检查与修复：ALTER COLLATION、REINDEX、系统信息函数。