PostgreSQL 排序规则支持详解

概述

排序规则（Collation）是 PostgreSQL 中一个强大的特性，它允许我们指定每列甚至每个操作的数据排序顺序和字符分类行为。这个特性解决了数据库在创建后无法更改 LC_COLLATE 和 LC_CTYPE 设置的限制问题，为多语言和国际化应用提供了灵活的解决方案。

TIP

解决的核心问题在传统的数据库系统中，排序规则在数据库创建时就确定了，无法灵活更改。PostgreSQL 的排序规则支持让我们可以：

为不同列指定不同的排序规则
在查询时动态指定排序规则
支持多语言应用的本地化需求

INFO

真实业务场景想象一个跨国电商平台：

德国站点：商品名称需要正确处理德文字符（ä, ö, ü）的排序
中国站点：商品名称按拼音顺序排列更符合用户习惯
美国站点：标准英文字母排序即可满足需求
搜索功能：需要不区分大小写的模糊匹配

使用 PostgreSQL 排序规则，一个数据库就可以同时满足所有需求。

1. 排序规则核心概念深入理解

1.1 可排序数据类型系统

1.2 表达式排序规则继承机制

在 PostgreSQL 中，每个可排序数据类型的表达式都有一个排序规则，其来源遵循以下规律：

sql

-- 创建演示表来理解排序规则继承
CREATE TABLE collation_demo (
    id SERIAL PRIMARY KEY,
    german_col text COLLATE "de_DE",      -- 列级别排序规则
    spanish_col text COLLATE "es_ES",     -- 列级别排序规则
    default_col text                      -- 使用数据库默认排序规则
);

-- 插入测试数据
INSERT INTO collation_demo (german_col, spanish_col, default_col) VALUES
('Müller', 'García', 'Smith'),
('Fischer', 'López', 'Johnson'),
('Äpfel', 'Núñez', 'Wilson');

排序规则继承规律分析：

表达式类型	排序规则来源	示例	说明
列引用	列定义的排序规则	`german_col` → `"de_DE"`	继承建表时指定的排序规则
字符串常量	数据类型默认排序规则	`'test'` → 数据库默认	使用当前数据库的默认设置
复杂表达式	从输入表达式派生	`german_col \|\| 'suffix'`	按特定规则组合得出

实际测试示例：

sql

-- 示例1：列引用使用列的排序规则
SELECT german_col,
       german_col < 'Nürnberg' AS "德文比较结果"
FROM collation_demo
WHERE german_col = 'Müller';

-- 输出：
-- german_col | 德文比较结果
-- -----------+---------------
-- Müller     | t
-- (1 row)

-- 示例2：常量使用默认排序规则
SELECT 'Müller' < 'Nürnberg' AS "默认排序规则比较";

-- 示例3：复杂表达式的排序规则派生
SELECT german_col || ' GmbH' AS "组合结果",
       pg_collation_for(german_col || ' GmbH') AS "派生的排序规则"
FROM collation_demo
LIMIT 1;

-- 输出：
-- 组合结果    | 派生的排序规则
-- -----------+---------------
-- Müller GmbH| "de_DE"
-- (1 row)

分析过程：

german_col 具有德文排序规则，在与字符串常量连接时，结果表达式继承了德文排序规则
pg_collation_for() 函数可以查看表达式实际使用的排序规则
这种继承机制确保了操作的一致性

1.3 排序规则的三种状态

PostgreSQL 中的表达式排序规则可能处于三种状态：

状态示例演示：

sql

-- 创建测试表演示三种状态
CREATE TABLE collation_states (
    id SERIAL PRIMARY KEY,
    col_a text COLLATE "de_DE",
    col_b text COLLATE "es_ES",
    col_c text  -- 使用默认排序规则
);

-- 插入测试数据
INSERT INTO collation_states VALUES
(1, 'Müller', 'García', 'Smith'),
(2, 'Fischer', 'López', 'Johnson');

状态 1：默认排序规则

sql

-- col_c 使用默认排序规则
SELECT col_c FROM collation_states ORDER BY col_c;
-- 使用数据库创建时指定的排序规则

状态 2：确定的排序规则

sql

-- col_a 有明确的德文排序规则
SELECT col_a FROM collation_states ORDER BY col_a;
-- 使用德文排序规则 "de_DE"

状态 3：不确定的排序规则（错误示例）

sql

-- 这会产生错误，因为两列有冲突的排序规则
-- SELECT col_a || col_b FROM collation_states;
-- ERROR: could not determine which collation to use for string concatenation

-- 正确的做法：显式指定排序规则
SELECT col_a COLLATE "de_DE" || col_b COLLATE "de_DE" AS "组合结果"
FROM collation_states;

1.4 排序规则在数据库操作中的应用场景

排序规则不仅影响 ORDER BY 子句，还会影响多种数据库操作：

详细应用示例：

sql

-- 创建多语言内容表
CREATE TABLE multilingual_content (
    id SERIAL PRIMARY KEY,
    title_en text COLLATE "en_US",
    title_de text COLLATE "de_DE",
    title_zh text COLLATE "zh_CN",
    content text,
    created_at timestamp DEFAULT NOW()
);

-- 插入包含特殊字符的测试数据
INSERT INTO multilingual_content (title_en, title_de, title_zh, content) VALUES
('About Us', 'Über uns', '关于我们', 'Company information'),
('Products', 'Produkte', '产品', 'Product catalog'),
('Contact', 'Kontakt', '联系我们', 'Contact details'),
('Support', 'Unterstützung', '支持', 'Help and support');

场景 1：排序操作

sql

-- 德文排序：正确处理 Ü 字符
SELECT title_de FROM multilingual_content ORDER BY title_de;

-- 输出顺序：
-- title_de
-- -----------
-- Kontakt
-- Produkte
-- Über uns        -- Ü 按德文规则排序
-- Unterstützung

场景 2：比较操作

sql

-- 使用德文排序规则进行比较
SELECT title_de,
       title_de < 'Produkte' AS "在Products之前",
       title_de = 'über uns' COLLATE "de_DE" AS "忽略大小写比较"
FROM multilingual_content;

-- 输出分析：
-- title_de      | 在Products之前 | 忽略大小写比较
-- -------------+---------------+---------------
-- Über uns      | f             | f
-- Produkte      | f             | f
-- Kontakt       | t             | f
-- Unterstützung | f             | f

场景 3：字符处理函数

sql

-- 德文排序规则影响大小写转换
SELECT title_de,
       upper(title_de) AS "大写转换",
       lower(title_de) AS "小写转换",
       initcap(title_de) AS "首字母大写"
FROM multilingual_content
WHERE title_de = 'Über uns';

-- 输出：
-- title_de | 大写转换 | 小写转换 | 首字母大写
-- ---------+---------+---------+-----------
-- Über uns | ÜBER UNS| über uns | Über Uns

场景 4：模式匹配

sql

-- 德文排序规则影响 LIKE 操作
SELECT title_de
FROM multilingual_content
WHERE title_de LIKE '%ü%';  -- 可以匹配 ü 和 Ü

-- 创建不区分大小写的搜索
SELECT title_de
FROM multilingual_content
WHERE lower(title_de) LIKE lower('%ÜBER%');

场景 5：聚合函数

sql

-- MIN/MAX 函数使用排序规则
SELECT
    MIN(title_de) AS "德文最小值",
    MAX(title_de) AS "德文最大值",
    MIN(title_en) AS "英文最小值",
    MAX(title_en) AS "英文最大值"
FROM multilingual_content;

-- 输出：
-- 德文最小值 | 德文最大值    | 英文最小值 | 英文最大值
-- -----------+--------------+-----------+-----------
-- Kontakt    | Unterstützung| About Us  | Support

分析过程：

德文排序中，Kontakt 排在最前，Unterstützung 排在最后
英文排序中，About Us 排在最前（A 开头），Support 排在最后（S 开头）
不同的排序规则产生了不同的最值结果

2. 排序规则组合与派生规律

2.1 排序规则组合的核心规则

当多个表达式需要组合时，PostgreSQL 使用严格的优先级规则来确定最终的排序规则：

2.2 实际场景演示

基于原始文档中的例子，让我们创建一个完整的演示环境：

sql

-- 创建演示表（来自原始文档）
CREATE TABLE test1 (
    a text COLLATE "de_DE",      -- 德文排序规则
    b text COLLATE "es_ES"       -- 西班牙文排序规则
);

-- 插入测试数据
INSERT INTO test1 VALUES
('Müller', 'García'),
('Äpfel', 'Núñez'),
('Fischer', 'López');

2.3 场景分析与解决方案

场景 1：隐式排序规则与默认排序规则组合

sql

-- ✅ 成功执行：隐式排序规则 + 默认排序规则
SELECT a < 'foo' AS "比较结果" FROM test1;

-- 输出：
-- 比较结果
-- --------
-- f       -- 'Müller' < 'foo' (false)
-- t       -- 'Äpfel' < 'foo' (true)
-- f       -- 'Fischer' < 'foo' (false)
-- (3 rows)

分析过程：

a 列具有隐式的德文排序规则 ("de_DE")
字符串常量 'foo' 使用默认排序规则
根据组合规则：隐式排序规则覆盖默认排序规则
比较操作使用德文排序规则执行

场景 2：显式排序规则覆盖隐式排序规则

sql

-- ✅ 成功执行：显式排序规则优先级最高
SELECT a < ('foo' COLLATE "fr_FR") AS "法文规则比较" FROM test1;

-- 输出：
-- 法文规则比较
-- -------------
-- f
-- t
-- f
-- (3 rows)

分析过程：

a 列的隐式德文排序规则被覆盖
显式指定的法文排序规则 ("fr_FR") 优先级更高
比较操作使用法文排序规则执行

场景 3：冲突的隐式排序规则

sql

-- ❌ 产生错误：冲突的隐式排序规则
-- SELECT a < b FROM test1;

-- 错误信息：
-- ERROR:  could not determine which collation to use for string comparison
-- HINT:  Use the COLLATE clause to set the collation explicitly.

问题分析：

a 列使用德文排序规则 ("de_DE")
b 列使用西班牙文排序规则 ("es_ES")
两个非默认的隐式排序规则冲突
PostgreSQL 无法确定使用哪个排序规则

解决方案：

sql

-- 方案1：显式指定排序规则给其中一列
SELECT a < b COLLATE "de_DE" AS "使用德文规则" FROM test1;

-- 方案2：显式指定排序规则给比较表达式
SELECT a COLLATE "de_DE" < b AS "使用德文规则2" FROM test1;

-- 方案3：两列都指定相同的排序规则
SELECT a COLLATE "C" < b COLLATE "C" AS "使用C规则" FROM test1;

-- 输出示例（使用德文规则）：
-- 使用德文规则
-- ------------
-- f
-- t
-- f
-- (3 rows)

场景 4：字符串连接操作的排序规则派生

sql

-- ✅ 成功执行：|| 运算符不关心排序规则
SELECT a || b AS "连接结果" FROM test1;

-- 输出：
-- 连接结果
-- -----------
-- MüllerGarcía
-- ÄpfelNúñez
-- FischerLópez
-- (3 rows)

但是在排序中会遇到问题：

sql

-- ❌ 产生错误：ORDER BY 需要确定的排序规则
-- SELECT * FROM test1 ORDER BY a || b;

-- ERROR:  could not determine which collation to use for string comparison
-- HINT:  Use the COLLATE clause to set the collation explicitly.

-- ✅ 正确方式：显式指定排序规则
SELECT * FROM test1 ORDER BY (a || b) COLLATE "de_DE";

-- 或者
SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";

2.4 复杂表达式的排序规则传播

为了更深入理解排序规则如何在复杂表达式中传播，让我们看一些高级示例：

sql

-- 创建更复杂的测试场景
CREATE TABLE complex_collation_test (
    id SERIAL PRIMARY KEY,
    german_name text COLLATE "de_DE",
    english_name text COLLATE "en_US",
    category text COLLATE "C",          -- 使用简单的C排序规则
    priority INTEGER
);

-- 插入测试数据
INSERT INTO complex_collation_test (german_name, english_name, category, priority) VALUES
('Müller GmbH', 'Mueller Company', 'CORP', 1),
('Bäckerei Schmidt', 'Schmidt Bakery', 'FOOD', 2),
('Café König', 'King Cafe', 'FOOD', 3),
('Öl & Gas AG', 'Oil & Gas Inc', 'ENERGY', 1);

示例 1：函数调用中的排序规则

sql

-- upper() 函数继承输入表达式的排序规则
SELECT
    german_name,
    upper(german_name) AS "德文大写",
    pg_collation_for(upper(german_name)) AS "排序规则"
FROM complex_collation_test
WHERE german_name LIKE '%ü%';

-- 输出：
-- german_name  | 德文大写      | 排序规则
-- ------------+--------------+-----------
-- Müller GmbH | MÜLLER GMBH  | "de_DE"
-- (1 row)

示例 2：条件表达式中的排序规则

sql

-- CASE 表达式中的排序规则派生
SELECT
    german_name,
    CASE
        WHEN priority = 1 THEN german_name || ' (优先)'
        ELSE german_name || ' (普通)'
    END AS "标记名称",
    pg_collation_for(
        CASE
            WHEN priority = 1 THEN german_name || ' (优先)'
            ELSE german_name || ' (普通)'
        END
    ) AS "结果排序规则"
FROM complex_collation_test
LIMIT 2;

-- 输出：
-- german_name  | 标记名称          | 结果排序规则
-- ------------+------------------+--------------
-- Müller GmbH | Müller GmbH (优先) | "de_DE"
-- Bäckerei... | Bäckerei... (普通) | "de_DE"

分析过程：

german_name 列具有德文排序规则
字符串常量使用默认排序规则
连接操作继承了德文排序规则（因为它是非默认的）
CASE 表达式的结果继承了分支表达式的排序规则

2.5 排序规则组合的性能影响

sql

-- 创建性能测试表
CREATE TABLE performance_demo (
    id SERIAL PRIMARY KEY,
    text_c text COLLATE "C",                    -- C排序规则（最快）
    text_default text,                          -- 默认排序规则
    text_german text COLLATE "de_DE",           -- 特定语言排序规则
    text_unicode text COLLATE "unicode"         -- Unicode排序规则（较慢）
);

-- 插入大量测试数据
INSERT INTO performance_demo (text_c, text_default, text_german, text_unicode)
SELECT
    'test_' || i,
    'test_' || i,
    'test_' || i,
    'test_' || i
FROM generate_series(1, 10000) i;

-- 性能对比测试
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM performance_demo WHERE text_c = 'test_5000';

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM performance_demo WHERE text_german = 'test_5000';

WARNING

性能提示排序规则的复杂性直接影响性能：

C/POSIX 排序规则：最快，按字节值比较
语言特定排序规则：中等性能，需要语言规则处理
Unicode/ICU 排序规则：相对较慢，但功能最强
非确定性排序规则：最慢，无法使用某些优化

在性能敏感的场景中，应优先考虑使用 C 排序规则。

2.6 排序规则组合的最佳实践

基于前面的分析，这里总结一些最佳实践：

TIP

最佳实践建议

1. 表设计阶段明确排序规则策略

sql

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    username text COLLATE "C",                  -- 用户名：简单快速
    display_name text COLLATE "unicode",        -- 显示名：支持多语言
    email text COLLATE "C",                     -- 邮箱：简单快速
    search_text text COLLATE case_insensitive   -- 搜索：大小写不敏感
);

2. 避免在同一操作中混合不同排序规则

sql

-- ❌ 避免这样做
SELECT col1 < col2 FROM mixed_table;  -- 可能出错

-- ✅ 推荐做法
SELECT col1 COLLATE "C" < col2 COLLATE "C" FROM mixed_table;

3. 为高频查询创建合适的索引

sql

-- 为特定排序规则创建索引
CREATE INDEX idx_german_name ON products (name COLLATE "de_DE");

-- 查询时使用相同的排序规则
SELECT * FROM products
WHERE name COLLATE "de_DE" = 'Müller'
ORDER BY name COLLATE "de_DE";

4. 使用 pg_collation_for() 函数调试排序规则

sql

-- 检查表达式的实际排序规则
SELECT pg_collation_for(complex_expression) FROM your_table;

3. 排序规则管理深入解析

3.1 排序规则提供程序对比

PostgreSQL 支持两种主要的排序规则提供程序，各有特色和适用场景：

特性	libc 提供程序	ICU 提供程序
依赖性	操作系统 C 库	ICU 国际化库
平台兼容性	平台相关，行为可能不同	平台无关，行为一致
编码关系	与特定编码绑定	编码无关
配置方式	LC_COLLATE + LC_CTYPE	命名排序器
版本稳定性	系统更新可能影响	ICU 版本变化可能影响
功能丰富度	基础功能	高级定制功能
性能	通常较快	功能丰富但相对较慢

3.2 标准排序规则详解

PostgreSQL 提供了几种在所有平台上都可用的标准排序规则：

3.2.1 核心标准排序规则

sql

-- 创建测试表来演示不同排序规则的行为
CREATE TABLE collation_comparison (
    id SERIAL PRIMARY KEY,
    test_text text,
    unicode_col text COLLATE "unicode",
    ucs_basic_col text COLLATE "ucs_basic",
    pg_c_utf8_col text COLLATE "pg_c_utf8",
    c_col text COLLATE "C",
    default_col text COLLATE "default"
);

-- 插入包含特殊字符和各种情况的测试数据
INSERT INTO collation_comparison (test_text) VALUES
('café'),
('CAFÉ'),
('cafe'),
('Café'),
('résumé'),
('RÉSUMÉ'),
('naïve'),
('中文测试'),
('日本語'),
('한국어'),
('🙂emoji');

-- 更新所有列为相同的测试数据
UPDATE collation_comparison SET
    unicode_col = test_text,
    ucs_basic_col = test_text,
    pg_c_utf8_col = test_text,
    c_col = test_text,
    default_col = test_text;

排序规则行为对比：

sql

-- 比较不同排序规则的排序行为
SELECT
    test_text,
    '---unicode---' as separator1,
    ROW_NUMBER() OVER (ORDER BY unicode_col) as unicode_rank,
    '---ucs_basic---' as separator2,
    ROW_NUMBER() OVER (ORDER BY ucs_basic_col) as ucs_basic_rank,
    '---pg_c_utf8---' as separator3,
    ROW_NUMBER() OVER (ORDER BY pg_c_utf8_col) as pg_c_utf8_rank,
    '---C---' as separator4,
    ROW_NUMBER() OVER (ORDER BY c_col) as c_rank
FROM collation_comparison
ORDER BY test_text;

各排序规则特点分析：

unicode排序规则ucs_basic排序规则pg_c_utf8排序规则C排序规则

sql

-- unicode: 使用 Unicode 整理算法，需要 ICU 支持
SELECT test_text FROM collation_comparison
ORDER BY unicode_col;

/*
特点：
- 遵循 Unicode 标准的自然语言排序
- 正确处理重音符号和特殊字符
- 支持多语言混合排序
- 性能相对较慢但最准确
*/

sql

-- ucs_basic: 按 Unicode 代码点值排序
SELECT test_text FROM collation_comparison
ORDER BY ucs_basic_col;

/*
特点：
- 简单的代码点数值排序
- 只有 ASCII 字母被视为字母
- 高效且稳定
- 仅适用于 UTF8 编码
*/

sql

-- pg_c_utf8: 代码点排序 + Unicode 大小写映射
SELECT test_text FROM collation_comparison
ORDER BY pg_c_utf8_col;

/*
特点：
- 按 Unicode 代码点排序
- 支持 Unicode 大小写转换
- 支持 POSIX 兼容的模式匹配
- 高效且稳定，适合现代 UTF8 应用
*/

sql

-- C/POSIX: 按字节值排序
SELECT test_text FROM collation_comparison
ORDER BY c_col;

/*
特点：
- 最快的排序方式
- 按字节值严格排序
- 只有 ASCII 字母被视为字母
- 行为可能因编码而异
*/

3.3 预定义排序规则的创建机制

3.3.1 libc 排序规则的创建

sql

-- 查看系统中可用的 libc 排序规则
SELECT
    collname as "排序规则名称",
    collprovider as "提供程序",
    collencoding as "编码ID",
    collcollate as "LC_COLLATE",
    collctype as "LC_CTYPE"
FROM pg_collation
WHERE collprovider = 'c'  -- libc 提供程序
    AND collname LIKE '%de%'  -- 德文相关
ORDER BY collname;

-- 输出示例：
/*
 排序规则名称  | 提供程序 | 编码ID |   LC_COLLATE    |    LC_CTYPE
--------------+----------+--------+-----------------+-----------------
 de_DE         | c        |      6 | de_DE.utf8      | de_DE.utf8
 de_DE.utf8    | c        |      6 | de_DE.utf8      | de_DE.utf8
*/

libc 排序规则命名规律：

sql

-- 演示简化名称和完整名称的关系
CREATE TABLE locale_demo (
    id SERIAL PRIMARY KEY,
    text_full text COLLATE "de_DE.utf8",     -- 完整名称
    text_short text COLLATE "de_DE"          -- 简化名称
);

-- 测试两者是否等效
INSERT INTO locale_demo VALUES (1, 'Müller', 'Müller');

-- 验证排序行为是否一致
SELECT
    text_full = text_short as "是否相等",
    text_full < 'Nürnberg' as "完整名称比较",
    text_short < 'Nürnberg' as "简化名称比较"
FROM locale_demo;

-- 输出：
/*
 是否相等 | 完整名称比较 | 简化名称比较
----------+-------------+-------------
 t        | t           | t
*/

批量导入新的系统排序规则：

sql

-- 当操作系统新增了区域设置后，可以批量导入
SELECT pg_import_system_collations('pg_catalog');

-- 查看新导入的排序规则
SELECT collname FROM pg_collation
WHERE collname NOT IN (
    SELECT collname FROM pg_collation_backup  -- 假设之前备份过
)
ORDER BY collname;

3.3.2 ICU 排序规则的创建

sql

-- 查看 ICU 排序规则的特点
SELECT
    collname as "排序规则名称",
    collprovider as "提供程序",
    colliculocale as "ICU区域设置"
FROM pg_collation
WHERE collprovider = 'i'  -- ICU 提供程序
    AND collname LIKE '%de%'
ORDER BY collname;

-- 输出示例：
/*
 排序规则名称 | 提供程序 | ICU区域设置
--------------+----------+------------
 de-x-icu      | i        | de
 de-AT-x-icu   | i        | de-AT
 de-CH-x-icu   | i        | de-CH
 de-DE-x-icu   | i        | de-DE
*/

ICU 排序规则命名解析：

排序规则名称	含义	说明
`de-x-icu`	德语，默认变体	通用德语排序规则
`de-AT-x-icu`	奥地利德语	奥地利地区的德语变体
`de-CH-x-icu`	瑞士德语	瑞士地区的德语变体
`de-DE-x-icu`	德国德语	德国地区的德语变体
`und-x-icu`	未定义（根排序规则）	语言无关的 Unicode 排序

测试不同 ICU 排序规则的差异：

sql

-- 创建 ICU 排序规则测试表
CREATE TABLE icu_comparison (
    id SERIAL PRIMARY KEY,
    text_root text COLLATE "und-x-icu",      -- 根排序规则
    text_de text COLLATE "de-x-icu",         -- 德语排序规则
    text_de_at text COLLATE "de-AT-x-icu"    -- 奥地利德语排序规则
);

-- 插入德语特殊字符测试数据
INSERT INTO icu_comparison (text_root, text_de, text_de_at) VALUES
('Müller', 'Müller', 'Müller'),
('Bäcker', 'Bäcker', 'Bäcker'),
('Österreich', 'Österreich', 'Österreich');

-- 比较不同 ICU 排序规则的排序结果
SELECT 'Root ICU' as collation_type, text_root as text_value
FROM icu_comparison ORDER BY text_root
UNION ALL
SELECT 'German ICU', text_de
FROM icu_comparison ORDER BY text_de
UNION ALL
SELECT 'Austrian ICU', text_de_at
FROM icu_comparison ORDER BY text_de_at;

3.4 创建自定义排序规则

3.4.1 基于 libc 的自定义排序规则

sql

-- 创建自定义德语排序规则
CREATE COLLATION custom_german (
    provider = libc,
    locale = 'de_DE'
);

-- 验证创建结果
SELECT collname, collprovider, collcollate, collctype
FROM pg_collation
WHERE collname = 'custom_german';

-- 测试自定义排序规则
CREATE TABLE german_test (
    id SERIAL PRIMARY KEY,
    company_name text COLLATE custom_german
);

INSERT INTO german_test (company_name) VALUES
('Müller GmbH'),
('Bäcker & Co'),
('Österreich AG'),
('Zürich Bank');

-- 按德语排序规则排序
SELECT company_name FROM german_test
ORDER BY company_name;

实际业务应用场景：

sql

-- 创建德国客户管理系统
CREATE TABLE german_customers (
    customer_id SERIAL PRIMARY KEY,
    last_name text COLLATE custom_german,
    first_name text COLLATE custom_german,
    company text COLLATE custom_german,
    city text COLLATE custom_german,
    postal_code char(5) COLLATE "C",      -- 邮编使用简单排序
    email text COLLATE "C",               -- 邮箱使用简单排序
    created_at timestamp DEFAULT NOW()
);

-- 插入德国客户数据
INSERT INTO german_customers (last_name, first_name, company, city, postal_code, email) VALUES
('Müller', 'Hans', 'Müller Maschinenbau GmbH', 'München', '80331', '[email protected]'),
('Bäcker', 'Anna', 'Bäckerei Schmidt', 'Berlin', '10115', '[email protected]'),
('Österreich', 'Klaus', 'Österreich Consulting', 'Hamburg', '20095', '[email protected]'),
('Zähringer', 'Maria', 'Zähringer & Partner', 'Frankfurt', '60311', '[email protected]');

-- 德语客户姓名排序（正确处理变音符号）
SELECT last_name, first_name, city
FROM german_customers
ORDER BY last_name, first_name;

-- 输出：
/*
 last_name   | first_name | city
-------------+------------+-----------
 Bäcker      | Anna       | Berlin
 Müller      | Hans       | München
 Österreich  | Klaus      | Hamburg
 Zähringer   | Maria      | Frankfurt
*/

-- 德语城市名排序
SELECT DISTINCT city FROM german_customers
ORDER BY city;

-- 输出：
/*
 city
-----------
 Berlin
 Frankfurt
 Hamburg
 München
*/

3.4.2 基于 ICU 的高级自定义排序规则

sql

-- 创建大小写不敏感的搜索排序规则
CREATE COLLATION case_insensitive (
    provider = icu,
    locale = 'und-u-ks-level2',
    deterministic = false
);

-- 创建忽略重音符号的排序规则
CREATE COLLATION ignore_accents (
    provider = icu,
    locale = 'und-u-ks-level1-kc-true',
    deterministic = false
);

-- 创建数字感知排序规则
CREATE COLLATION numeric_aware (
    provider = icu,
    locale = 'und-u-kn'
);

-- 测试这些自定义排序规则
CREATE TABLE advanced_search (
    id SERIAL PRIMARY KEY,
    product_name text,
    version_number text
);

INSERT INTO advanced_search (product_name, version_number) VALUES
('café', 'v1.2.10'),
('CAFE', 'v1.2.2'),
('Café', 'v1.10.1'),
('naïve', 'v2.1.5'),
('naive', 'v2.11.0'),
('NAÏVE', 'v10.0.1');

大小写不敏感搜索示例：

sql

-- 传统搜索：区分大小写
SELECT product_name FROM advanced_search
WHERE product_name = 'cafe';
-- 结果：无匹配

-- 大小写不敏感搜索
SELECT product_name FROM advanced_search
WHERE product_name = 'cafe' COLLATE case_insensitive;
-- 结果：匹配 'café', 'CAFE', 'Café'

-- 输出：
/*
 product_name
--------------
 café
 CAFE
 Café
*/

忽略重音符号搜索示例：

sql

-- 忽略重音符号的搜索
SELECT product_name FROM advanced_search
WHERE product_name = 'naive' COLLATE ignore_accents;
-- 结果：匹配所有 naive 变体

-- 输出：
/*
 product_name
--------------
 naïve
 naive
 NAÏVE
*/

数字感知排序示例：

sql

-- 传统字符串排序（字典序）
SELECT version_number FROM advanced_search
ORDER BY version_number;
-- 结果：v1.10.1, v1.2.10, v1.2.2, v10.0.1, v2.1.5, v2.11.0

-- 数字感知排序
SELECT version_number FROM advanced_search
ORDER BY version_number COLLATE numeric_aware;
-- 结果：v1.2.2, v1.2.10, v1.10.1, v2.1.5, v2.11.0, v10.0.1

-- 输出对比：
/*
传统排序          | 数字感知排序
------------------+------------------
v1.10.1           | v1.2.2
v1.2.10           | v1.2.10
v1.2.2            | v1.10.1
v10.0.1           | v2.1.5
v2.1.5            | v2.11.0
v2.11.0           | v10.0.1
*/

3.4.3 复制现有排序规则

sql

-- 从现有排序规则创建别名，提高可读性
CREATE COLLATION german FROM "de_DE";
CREATE COLLATION french FROM "fr-x-icu";
CREATE COLLATION chinese FROM "zh_CN";

-- 创建业务友好的排序规则名称
CREATE COLLATION user_search FROM case_insensitive;
CREATE COLLATION product_name_sort FROM ignore_accents;
CREATE COLLATION version_sort FROM numeric_aware;

-- 验证别名排序规则
SELECT
    collname,
    collprovider,
    CASE
        WHEN collprovider = 'c' THEN collcollate
        WHEN collprovider = 'i' THEN colliculocale
    END as locale_info
FROM pg_collation
WHERE collname IN ('german', 'french', 'chinese', 'user_search', 'product_name_sort', 'version_sort')
ORDER BY collname;

业务应用示例：

sql

-- 使用业务友好的排序规则名称
CREATE TABLE international_products (
    product_id SERIAL PRIMARY KEY,
    name_de text COLLATE german,
    name_fr text COLLATE french,
    name_zh text COLLATE chinese,
    searchable_name text COLLATE user_search,
    display_name text COLLATE product_name_sort,
    version text COLLATE version_sort,
    created_at timestamp DEFAULT NOW()
);

-- 这样的设计让表结构更易理解和维护
INSERT INTO international_products (name_de, name_fr, name_zh, searchable_name, display_name, version) VALUES
('Mobiltelefon', 'Téléphone Mobile', '手机', 'SMARTPHONE', 'SmartPhone Pro', 'v2.1.0'),
('Computer', 'Ordinateur', '计算机', 'LAPTOP', 'LapTop Ultra', 'v1.5.10'),
('Kamera', 'Appareil Photo', '相机', 'CAMERA', 'Digital Camera X', 'v3.0.2');

-- 多语言排序测试
SELECT name_de FROM international_products ORDER BY name_de;
SELECT name_fr FROM international_products ORDER BY name_fr;
SELECT name_zh FROM international_products ORDER BY name_zh;

3.5 非确定性排序规则深入解析

非确定性排序规则是 PostgreSQL 的一个强大特性，它允许字符串即使由不同字节组成也可能被认为是相等的。

3.5.1 确定性 vs 非确定性概念

3.5.2 非确定性排序规则的创建和应用

sql

-- 创建各种非确定性排序规则
CREATE COLLATION case_insensitive_nd (
    provider = icu,
    locale = 'und-u-ks-level2',
    deterministic = false
);

CREATE COLLATION accent_insensitive_nd (
    provider = icu,
    locale = 'und-u-ks-level1-kc-true',
    deterministic = false
);

CREATE COLLATION unicode_normalized_nd (
    provider = icu,
    locale = 'und',
    deterministic = false
);

-- 创建测试表
CREATE TABLE nondeterministic_demo (
    id SERIAL PRIMARY KEY,
    case_test text COLLATE case_insensitive_nd,
    accent_test text COLLATE accent_insensitive_nd,
    unicode_test text COLLATE unicode_normalized_nd,
    standard_text text COLLATE "C"
);

实际测试示例：

sql

-- 插入各种测试数据
INSERT INTO nondeterministic_demo (case_test, accent_test, unicode_test, standard_text) VALUES
('Hello', 'café', 'é', 'Hello'),           -- 正常文本
('HELLO', 'cafe', 'é', 'HELLO'),           -- 大小写变体
('hello', 'CAFÉ', 'e' || U&'\0301', 'hello'), -- 组合字符
('Hello', 'Cafe', 'ê', 'Hello');           -- 其他变体

-- 测试大小写不敏感比较
SELECT
    case_test,
    case_test = 'hello' AS "匹配hello",
    case_test = 'HELLO' AS "匹配HELLO",
    case_test = 'Hello' AS "匹配Hello"
FROM nondeterministic_demo;

-- 输出：
/*
 case_test | 匹配hello | 匹配HELLO | 匹配Hello
-----------+-----------+-----------+-----------
 Hello     | t         | t         | t
 HELLO     | t         | t         | t
 hello     | t         | t         | t
 Hello     | t         | t         | t
*/

-- 测试重音不敏感比较
SELECT
    accent_test,
    accent_test = 'cafe' AS "匹配cafe",
    accent_test = 'café' AS "匹配café"
FROM nondeterministic_demo;

-- 输出：
/*
 accent_test | 匹配cafe | 匹配café
-------------+-----------+-----------
 café        | t         | t
 cafe        | t         | t
 CAFÉ        | t         | t
 Cafe        | t         | t
*/

3.5.3 Unicode 规范化处理

sql

-- 演示 Unicode 规范化的重要性
WITH unicode_forms AS (
    SELECT
        'é' as nfc_form,                    -- NFC: 预组合形式
        'e' || U&'\0301' as nfd_form       -- NFD: 分解形式
)
SELECT
    nfc_form,
    nfd_form,
    nfc_form = nfd_form AS "标准比较",
    nfc_form = nfd_form COLLATE unicode_normalized_nd AS "规范化比较",
    length(nfc_form) AS "NFC长度",
    length(nfd_form) AS "NFD长度"
FROM unicode_forms;

-- 输出：
/*
 nfc_form | nfd_form | 标准比较 | 规范化比较 | NFC长度 | NFD长度
----------+----------+----------+------------+---------+---------
 é        | é        | f        | t          | 1       | 2
*/

分析过程：

NFC 形式使用一个预组合的字符 é
NFD 形式使用基础字符 e + 组合重音符 ◌́
标准比较认为它们不同（不同的字节序列）
非确定性排序规则正确识别它们在语义上相同

3.5.4 性能影响和限制

WARNING

性能和功能限制

非确定性排序规则虽然功能强大，但有一些重要限制：

性能影响：

B-tree 索引无法使用重复数据删除
字符串比较操作更复杂，耗时更长
某些查询优化无法应用

功能限制：

模式匹配操作（LIKE、正则表达式）不支持
某些聚合操作可能受影响
全文搜索功能可能不兼容

sql

-- 性能测试示例
CREATE TABLE performance_comparison (
    id SERIAL PRIMARY KEY,
    deterministic_col text COLLATE "C",
    nondeterministic_col text COLLATE case_insensitive_nd
);

-- 插入大量测试数据
INSERT INTO performance_comparison (deterministic_col, nondeterministic_col)
SELECT
    'test_' || i,
    'Test_' || i
FROM generate_series(1, 100000) i;

-- 创建索引
CREATE INDEX idx_det ON performance_comparison(deterministic_col);
CREATE INDEX idx_nondet ON performance_comparison(nondeterministic_col);

-- 性能对比测试
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM performance_comparison
WHERE deterministic_col = 'test_50000';

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM performance_comparison
WHERE nondeterministic_col = 'test_50000';

3.5.5 实际业务应用场景

场景 1：用户搜索系统

sql

-- 创建用户友好的搜索系统
CREATE TABLE user_search_system (
    user_id SERIAL PRIMARY KEY,
    username text COLLATE "C",                    -- 用户名：精确匹配
    display_name text COLLATE case_insensitive_nd, -- 显示名：大小写不敏感
    search_terms text COLLATE accent_insensitive_nd, -- 搜索词：忽略重音
    email text COLLATE "C"                        -- 邮箱：精确匹配
);

-- 插入测试用户
INSERT INTO user_search_system (username, display_name, search_terms, email) VALUES
('john_doe', 'John Doe', 'software developer', '[email protected]'),
('jane_smith', 'JANE SMITH', 'ui designer café', '[email protected]'),
('maría_garcía', 'María García', 'project manager naïve', '[email protected]');

-- 用户搜索：不区分大小写
SELECT username, display_name
FROM user_search_system
WHERE display_name = 'john doe';  -- 匹配 'John Doe'

-- 内容搜索：忽略重音符号
SELECT username, search_terms
FROM user_search_system
WHERE search_terms LIKE '%cafe%';  -- 匹配包含 'café' 的记录

场景 2：国际化产品名称管理

sql

-- 创建支持多语言的产品名称系统
CREATE TABLE international_products_advanced (
    product_id SERIAL PRIMARY KEY,
    sku text COLLATE "C",                         -- SKU：精确匹配
    name_display text COLLATE unicode_normalized_nd, -- 显示名：Unicode规范化
    search_name text COLLATE case_insensitive_nd,    -- 搜索名：大小写不敏感
    description text COLLATE accent_insensitive_nd,  -- 描述：重音不敏感
    category text COLLATE "C"                      -- 分类：精确匹配
);

-- 插入包含各种Unicode字符的产品
INSERT INTO international_products_advanced (sku, name_display, search_name, description, category) VALUES
('PHONE001', 'Smartphone é', 'Smartphone E', 'Advanced smartphone with café features', 'ELECTRONICS'),
('BOOK001', 'Résumé Guide', 'Resume Guide', 'Complete résumé writing guide', 'BOOKS'),
('FOOD001', 'Café Latte', 'Cafe Latte', 'Premium café latte blend', 'BEVERAGES');

-- 搜索测试
SELECT sku, name_display, search_name
FROM international_products_advanced
WHERE search_name = 'smartphone e';  -- 匹配 'Smartphone E'

SELECT sku, description
FROM international_products_advanced
WHERE description LIKE '%cafe%';     -- 匹配包含 'café' 的描述

4. ICU 自定义排序规则详解

4.1 ICU 比较级别（Strength Levels）系统

ICU 将字符串比较分为多个级别，每个级别关注不同的文本特征。理解这些级别对于创建合适的自定义排序规则至关重要。

4.1.1 级别详细说明和测试

sql

-- 创建各个级别的排序规则用于测试
CREATE COLLATION level1_test (
    provider = icu,
    deterministic = false,
    locale = 'und-u-ks-level1'
);

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

CREATE COLLATION level3_test (
    provider = icu,
    deterministic = false,
    locale = 'und-u-ks-level3'
);

CREATE COLLATION level4_test (
    provider = icu,
    deterministic = false,
    locale = 'und-u-ka-shifted-ks-level4'
);

CREATE COLLATION identical_test (
    provider = icu,
    deterministic = false,
    locale = 'und-u-ka-shifted-ks-identic'
);

-- 创建全面的测试表
CREATE TABLE icu_levels_demo (
    id SERIAL PRIMARY KEY,
    test_string text,
    description text
);

-- 插入各种测试字符串
INSERT INTO icu_levels_demo (test_string, description) VALUES
('cafe', '基础字符串'),
('café', '带重音符号'),
('CAFE', '大写版本'),
('CAFÉ', '大写带重音'),
('ca-fe', '带连字符'),
('ca fe', '带空格'),
('café' || U&'\2063', '带不可见分隔符'),  -- U+2063 是不可见的分隔符
('Cafe', '首字母大写');

级别测试和结果分析：

sql

-- Level 1 测试：只考虑基本字符
WITH level1_comparison AS (
    SELECT
        test_string,
        description,
        test_string = 'cafe' COLLATE level1_test AS "Level1相等"
    FROM icu_levels_demo
)
SELECT * FROM level1_comparison ORDER BY id;

-- 输出：
/*
 test_string | description      | Level1相等
-------------+------------------+-----------
 cafe        | 基础字符串       | t
 café        | 带重音符号       | t  ← 忽略重音
 CAFE        | 大写版本         | t  ← 忽略大小写
 CAFÉ        | 大写带重音       | t  ← 忽略重音和大小写
 ca-fe       | 带连字符         | f  ← 不同的基本字符
 ca fe       | 带空格           | f  ← 不同的基本字符
 café⁣       | 带不可见分隔符   | t  ← 忽略不可见字符
 Cafe        | 首字母大写       | t  ← 忽略大小写
*/

sql

-- Level 2 测试：考虑重音符号，忽略大小写
WITH level2_comparison AS (
    SELECT
        test_string,
        description,
        test_string = 'cafe' COLLATE level2_test AS "Level2_cafe相等",
        test_string = 'café' COLLATE level2_test AS "Level2_café相等"
    FROM icu_levels_demo
)
SELECT * FROM level2_comparison ORDER BY id;

-- 输出：
/*
 test_string | description      | Level2_cafe相等 | Level2_café相等
-------------+------------------+----------------+----------------
 cafe        | 基础字符串       | t              | f
 café        | 带重音符号       | f              | t  ← 重音敏感
 CAFE        | 大写版本         | t              | f  ← 大小写不敏感
 CAFÉ        | 大写带重音       | f              | t  ← 重音敏感，大小写不敏感
*/

sql

-- Level 3 测试：考虑大小写和重音符号
WITH level3_comparison AS (
    SELECT
        test_string,
        description,
        test_string = 'cafe' COLLATE level3_test AS "Level3_cafe相等",
        test_string = 'CAFE' COLLATE level3_test AS "Level3_CAFE相等"
    FROM icu_levels_demo
)
SELECT * FROM level3_comparison ORDER BY id;

-- 输出：
/*
 test_string | description      | Level3_cafe相等 | Level3_CAFE相等
-------------+------------------+----------------+----------------
 cafe        | 基础字符串       | t              | f
 café        | 带重音符号       | f              | f
 CAFE        | 大写版本         | f              | t  ← 大小写敏感
 CAFÉ        | 大写带重音       | f              | f
*/

4.2 ICU 高级自定义选项

4.2.1 数字感知排序

数字感知排序解决了版本号、文件名等包含数字的字符串排序问题：

sql

-- 创建数字感知排序规则
CREATE COLLATION numeric_aware_sort (
    provider = icu,
    deterministic = false,
    locale = 'und-u-kn'  -- kn 表示 numeric
);

-- 创建文件版本管理演示
CREATE TABLE file_versions (
    id SERIAL PRIMARY KEY,
    filename text COLLATE numeric_aware_sort,
    version text COLLATE numeric_aware_sort,
    size_mb decimal,
    upload_date timestamp DEFAULT NOW()
);

-- 插入文件版本数据
INSERT INTO file_versions (filename, version, size_mb) VALUES
('report_1.pdf', 'v1.0.1', 2.5),
('report_2.pdf', 'v1.0.10', 3.2),
('report_10.pdf', 'v1.0.2', 2.8),
('report_11.pdf', 'v1.0.11', 3.5),
('document_1_final.doc', 'v2.1.0', 1.2),
('document_2_final.doc', 'v2.10.0', 1.8),
('document_10_final.doc', 'v2.2.0', 1.5);

-- 传统字符串排序 vs 数字感知排序对比
SELECT 'Traditional String Sort' as sort_type, filename
FROM file_versions
ORDER BY filename COLLATE "C"
UNION ALL
SELECT '---', '---'
UNION ALL
SELECT 'Numeric Aware Sort', filename
FROM file_versions
ORDER BY filename COLLATE numeric_aware_sort;

-- 输出对比：
/*
 sort_type             | filename
-----------------------+----------------------
 Traditional String    | document_10_final.doc  ← 10在1后面
 Traditional String    | document_1_final.doc
 Traditional String    | document_2_final.doc
 Traditional String    | report_1.pdf
 Traditional String    | report_10.pdf         ← 10在2前面
 Traditional String    | report_11.pdf
 Traditional String    | report_2.pdf
 ---                   | ---
 Numeric Aware Sort    | document_1_final.doc   ← 正确的数字顺序
 Numeric Aware Sort    | document_2_final.doc
 Numeric Aware Sort    | document_10_final.doc
 Numeric Aware Sort    | report_1.pdf
 Numeric Aware Sort    | report_2.pdf          ← 正确的数字顺序
 Numeric Aware Sort    | report_10.pdf
 Numeric Aware Sort    | report_11.pdf
*/

-- 版本号排序演示
SELECT version FROM file_versions
ORDER BY version COLLATE numeric_aware_sort;

-- 输出：
/*
 version
---------
 v1.0.1
 v1.0.2    ← 正确排在v1.0.10之前
 v1.0.10
 v1.0.11
 v2.1.0
 v2.2.0    ← 正确排在v2.10.0之前
 v2.10.0
*/

4.2.2 忽略标点符号和空格

sql

-- 创建忽略标点符号的排序规则
CREATE COLLATION ignore_punctuation (
    provider = icu,
    deterministic = false,
    locale = 'und-u-ka-shifted'  -- ka-shifted 忽略标点和空格
);

-- 创建标点符号测试表
CREATE TABLE punctuation_test (
    id SERIAL PRIMARY KEY,
    company_name text COLLATE ignore_punctuation,
    formatted_name text COLLATE "C"
);

-- 插入包含各种标点符号的公司名称
INSERT INTO punctuation_test (company_name, formatted_name) VALUES
('Apple Inc.', 'Apple Inc.'),
('Apple-Inc', 'Apple-Inc'),
('Apple_Inc', 'Apple_Inc'),
('Apple Inc', 'Apple Inc'),
('Co-operative Bank', 'Co-operative Bank'),
('Cooperative Bank', 'Cooperative Bank'),
('Co operative Bank', 'Co operative Bank'),
('[email protected]', '[email protected]'),
('userdomain.com', 'userdomain.com');

-- 测试标点符号忽略效果
SELECT
    company_name,
    company_name = 'Apple Inc' AS "匹配Apple Inc",
    company_name = 'Cooperative Bank' AS "匹配Cooperative Bank",
    company_name = 'userdomain.com' AS "匹配userdomain.com"
FROM punctuation_test;

-- 输出：
/*
 company_name        | 匹配Apple Inc | 匹配Cooperative Bank | 匹配userdomain.com
---------------------+---------------+---------------------+-------------------
 Apple Inc.          | t             | f                   | f
 Apple-Inc           | t             | f                   | f
 Apple_Inc           | t             | f                   | f
 Apple Inc           | t             | f                   | f
 Co-operative Bank   | f             | t                   | f
 Cooperative Bank    | f             | t                   | f
 Co operative Bank   | f             | t                   | f
 [email protected]     | f             | f                   | t
 userdomain.com      | f             | f                   | t
*/

分析过程：

所有 Apple 相关的变体都被认为等同于 'Apple Inc'
不同标点符号表示的 Cooperative Bank 都被认为相等
邮箱地址中的 @ 符号被忽略

性能考虑和最佳实践

索引与排序规则

sql

-- 为特定排序规则创建索引
CREATE INDEX idx_products_name_de
ON products (name_de COLLATE "de_DE.utf8");

-- 查询时必须使用相同的排序规则才能使用索引
SELECT * FROM products
WHERE name_de COLLATE "de_DE.utf8" = 'Äpfel'
ORDER BY name_de COLLATE "de_DE.utf8";

WARNING

性能注意事项

非确定性排序规则（deterministic = false）会带来性能开销：

B-tree 索引无法使用重复数据删除
某些操作无法使用索引（如模式匹配）
比较操作更加复杂

建议仅在明确需要时使用非确定性排序规则。

最佳实践建议

设计阶段确定排序规则

sql

-- 在创建表时就明确指定
CREATE TABLE international_users (
    id SERIAL PRIMARY KEY,
    username text COLLATE "C",  -- 用户名使用简单排序
    display_name text COLLATE "und-u-ks-level2",  -- 显示名不区分大小写
    email text COLLATE "C"  -- 邮箱使用简单排序
);

避免混合使用不同排序规则

sql

-- ❌ 不推荐：容易产生错误
SELECT a.name, b.name
FROM table_a a, table_b b
WHERE a.name = b.name;  -- 如果排序规则不同会报错

-- ✅ 推荐：明确指定排序规则
SELECT a.name, b.name
FROM table_a a, table_b b
WHERE a.name COLLATE "C" = b.name COLLATE "C";

根据应用场景选择合适的排序规则

应用场景	推荐排序规则	原因
用户名、邮箱	`C` 或 `POSIX`	简单、快速、一致
用户显示名	`und-u-ks-level2`	大小写不敏感
搜索功能	`und-u-ks-level1`	忽略大小写和重音
文件名排序	`und-u-kn`	数字感知排序
多语言内容	对应语言的排序规则	符合用户期望

故障排除

常见错误及解决方案

错误 1：排序规则冲突

sql

-- 错误示例
SELECT column1 < column2 FROM mixed_collation_table;
-- ERROR: could not determine which collation to use for string comparison

-- 解决方案
SELECT column1 COLLATE "C" < column2 COLLATE "C"
FROM mixed_collation_table;

错误 2：排序规则不存在

sql

-- 查看可用的排序规则
SELECT collname FROM pg_collation WHERE collname LIKE '%de%';

-- 或者导入系统排序规则
SELECT pg_import_system_collations('pg_catalog');

错误 3：编码不兼容

sql

-- ICU 排序规则在某些编码下不可用
-- 检查数据库编码
SELECT current_setting('server_encoding');

-- 使用适合当前编码的排序规则

调试技巧

sql

-- 查看表达式的排序规则
SELECT pg_typeof('hello' COLLATE "de_DE.utf8");

-- 比较两个排序规则的行为
WITH test_data AS (
    SELECT unnest(ARRAY['ä', 'a', 'z', 'A']) as text_val
)
SELECT
    text_val,
    text_val COLLATE "C" as c_collation,
    text_val COLLATE "de_DE.utf8" as de_collation
FROM test_data
ORDER BY text_val COLLATE "de_DE.utf8";

总结

PostgreSQL 的排序规则功能为多语言应用提供了强大而灵活的支持。通过合理使用排序规则，可以：

提供本地化的用户体验：不同地区用户看到符合其语言习惯的排序
支持复杂的搜索需求：大小写不敏感、重音不敏感等搜索
处理特殊的排序要求：数字感知排序、自定义排序顺序
保证数据一致性：通过明确的排序规则避免歧义

在实际应用中，建议在设计阶段就确定排序规则策略，并在整个应用中保持一致性。合理使用排序规则不仅能提升用户体验，还能避免潜在的数据处理问题。

PostgreSQL 排序规则支持详解 ​

概述 ​

1. 排序规则核心概念深入理解 ​

1.1 可排序数据类型系统 ​

1.2 表达式排序规则继承机制 ​

1.3 排序规则的三种状态 ​

1.4 排序规则在数据库操作中的应用场景 ​

2. 排序规则组合与派生规律 ​

2.1 排序规则组合的核心规则 ​

2.2 实际场景演示 ​

2.3 场景分析与解决方案 ​

2.4 复杂表达式的排序规则传播 ​

2.5 排序规则组合的性能影响 ​

2.6 排序规则组合的最佳实践 ​

3. 排序规则管理深入解析 ​

3.1 排序规则提供程序对比 ​

3.2 标准排序规则详解 ​

3.2.1 核心标准排序规则 ​

3.3 预定义排序规则的创建机制 ​

3.3.1 libc 排序规则的创建 ​

3.3.2 ICU 排序规则的创建 ​

3.4 创建自定义排序规则 ​

3.4.1 基于 libc 的自定义排序规则 ​

3.4.2 基于 ICU 的高级自定义排序规则 ​

3.4.3 复制现有排序规则 ​

3.5 非确定性排序规则深入解析 ​

3.5.1 确定性 vs 非确定性概念 ​

3.5.2 非确定性排序规则的创建和应用 ​

3.5.3 Unicode 规范化处理 ​

3.5.4 性能影响和限制 ​

3.5.5 实际业务应用场景 ​

4. ICU 自定义排序规则详解 ​

4.1 ICU 比较级别（Strength Levels）系统 ​

4.1.1 级别详细说明和测试 ​

4.2 ICU 高级自定义选项 ​

4.2.1 数字感知排序 ​

4.2.2 忽略标点符号和空格 ​

性能考虑和最佳实践 ​

索引与排序规则 ​

最佳实践建议 ​

故障排除 ​

常见错误及解决方案 ​

调试技巧 ​

总结 ​

PostgreSQL 排序规则支持详解

概述

1. 排序规则核心概念深入理解

1.1 可排序数据类型系统

1.2 表达式排序规则继承机制

1.3 排序规则的三种状态

1.4 排序规则在数据库操作中的应用场景

2. 排序规则组合与派生规律

2.1 排序规则组合的核心规则

2.2 实际场景演示

2.3 场景分析与解决方案

2.4 复杂表达式的排序规则传播

2.5 排序规则组合的性能影响

2.6 排序规则组合的最佳实践

3. 排序规则管理深入解析

3.1 排序规则提供程序对比

3.2 标准排序规则详解

3.2.1 核心标准排序规则

3.3 预定义排序规则的创建机制

3.3.1 libc 排序规则的创建

3.3.2 ICU 排序规则的创建

3.4 创建自定义排序规则

3.4.1 基于 libc 的自定义排序规则

3.4.2 基于 ICU 的高级自定义排序规则

3.4.3 复制现有排序规则

3.5 非确定性排序规则深入解析

3.5.1 确定性 vs 非确定性概念

3.5.2 非确定性排序规则的创建和应用

3.5.3 Unicode 规范化处理

3.5.4 性能影响和限制

3.5.5 实际业务应用场景

4. ICU 自定义排序规则详解

4.1 ICU 比较级别（Strength Levels）系统

4.1.1 级别详细说明和测试

4.2 ICU 高级自定义选项

4.2.1 数字感知排序

4.2.2 忽略标点符号和空格

性能考虑和最佳实践

索引与排序规则

最佳实践建议

故障排除

常见错误及解决方案

调试技巧

总结