登录24.2. 排序规则支持#
排序规则功能允许按列甚至按操作指定数据排序顺序和字符分类行为。这缓解了数据库创建后无法更改其LC_COLLATE和LC_CTYPE设置的限制。
24.2.1. 概念#
从概念上讲，可排序数据类型的每个表达式都有一个排序规则。（内置的可排序数据类型为text、varchar和char。用户定义的基本类型也可以标记为可排序，当然，可排序数据类型上的域也是可排序的。）如果表达式是列引用，则表达式的排序规则是列的已定义排序规则。如果表达式是常量，则排序规则是常量数据类型的默认排序规则。更复杂表达式的排序规则是从其输入的排序规则派生的，如下所述。
表达式的排序规则可以是“default”排序规则，这意味着为数据库定义的区域设置。表达式的排序规则也可能是不确定的。在这种情况下，需要知道排序规则的排序操作和其他操作将失败。
当数据库系统必须执行排序或字符分类时，它将使用输入表达式的排序规则。例如，使用ORDER BY子句和函数或运算符调用（例如<）时，就会发生这种情况。要应用于ORDER BY子句的排序规则仅仅是排序键的排序规则。要应用于函数或运算符调用的排序规则是从参数派生的，如下所述。除了比较运算符之外，排序规则还由在大小写字母之间转换的函数（例如lower、upper和initcap）、模式匹配运算符以及to_char及相关函数考虑在内。
对于函数或运算符调用，通过检查参数排序规则派生的排序规则在运行时用于执行指定的操作。如果函数或运算符调用的结果是可排序数据类型，则排序规则也会在解析时用作函数或运算符表达式的已定义排序规则，以防有需要了解其排序规则的周围表达式。
表达式的排序规则派生可以是隐式的或显式的。此区别会影响在表达式中出现多个不同排序规则时如何组合排序规则。当使用COLLATE子句时，会发生显式排序规则派生；所有其他排序规则派生都是隐式的。当需要组合多个排序规则时（例如在函数调用中），将使用以下规则
如果任何输入表达式具有显式排序规则派生，则输入表达式中的所有显式派生排序规则必须相同，否则会引发错误。如果存在任何显式派生排序规则，那就是排序规则组合的结果。
否则，所有输入表达式都必须具有相同的隐式排序规则派生或默认排序规则。如果存在任何非默认排序规则，则这是排序规则组合的结果。否则，结果是默认排序规则。
如果输入表达式之间存在冲突的非默认隐式排序规则，则组合被视为具有不确定的排序规则。除非被调用的特定函数需要了解它应该应用的排序规则，否则这不是一个错误条件。如果需要，将在运行时引发错误。
例如，考虑此表定义
CREATE TABLE test1 (
a text COLLATE "de_DE",
b text COLLATE "es_ES",
...
);然后在
SELECT a < 'foo' FROM test1;<比较根据de_DE规则执行，因为表达式将隐式派生的排序规则与默认排序规则组合在一起。但在
SELECT a < ('foo' COLLATE "fr_FR") FROM test1;比较使用fr_FR规则执行，因为显式排序规则派生覆盖了隐式派生。此外，给定
SELECT a < b FROM test1;解析器无法确定要应用哪个排序规则，因为a和b列具有冲突的隐式排序规则。由于<运算符确实需要知道要使用哪个排序规则，因此这将导致错误。可以通过将显式排序规则说明符附加到任一输入表达式来解决此错误，因此
SELECT a < b COLLATE "de_DE" FROM test1;或等效地
SELECT a COLLATE "de_DE" < b FROM test1;另一方面，结构上类似的情况
SELECT a || b FROM test1;不会导致错误，因为||运算符不关心排序规则：无论排序规则如何，其结果都是相同的。
分配给函数或运算符的组合输入表达式的排序规则也考虑应用于函数或运算符的结果，如果函数或运算符提供可排序数据类型的结果。因此，在
SELECT * FROM test1 ORDER BY a || 'foo';排序将根据de_DE规则进行。但此查询
SELECT * FROM test1 ORDER BY a || b;会导致错误，因为即使||运算符不需要知道排序规则，但ORDER BY子句需要。与以前一样，可以使用显式排序规则说明符解决冲突
SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";24.2.2. 管理排序规则#
排序规则是一个 SQL 模式对象，它将 SQL 名称映射到操作系统中安装的库提供的区域设置。排序规则定义有一个提供程序，它指定哪个库提供区域设置数据。一个标准提供程序名称是libc，它使用操作系统 C 库提供的区域设置。这些是操作系统提供的多数工具使用的区域设置。另一个提供程序是icu，它使用外部 ICU库。仅当在构建 PostgreSQL 时配置了对 ICU 的支持时，才能使用 ICU 区域设置。
由libc提供的排序对象映射到LC_COLLATE和LC_CTYPE设置的组合，如系统库调用setlocale()所接受的。（顾名思义，排序的主要目的是设置LC_COLLATE，它控制排序顺序。但在实践中，很少需要将LC_CTYPE设置为不同于LC_COLLATE的值，因此将它们收集在一个概念下比创建另一个基础设施来设置每个表达式的LC_CTYPE更加方便。）此外，libc排序与字符集编码相关（参见第 24.3 节）。对于不同的编码，可能存在相同的排序名称。
由icu提供的排序对象映射到 ICU 库提供的命名排序器。ICU 不支持单独的“排序”和“字符类型”设置，因此它们始终相同。此外，ICU 排序与编码无关，因此数据库中给定名称的 ICU 排序始终只有一个。
24.2.2.1. 标准排序#
在所有平台上，都可以使用名为default、C和POSIX的排序。根据操作系统支持，可能可以使用其他排序。在数据库创建时，default排序会选择指定的LC_COLLATE和LC_CTYPE值。C和POSIX排序都指定了“传统 C”行为，其中仅将 ASCII 字母“A”到“Z”视为字母，并且严格按字符代码字节值进行排序。
注意
C和POSIX区域设置可能会根据数据库编码而表现不同。
此外，还有两个 SQL 标准排序名称可用
unicode此排序使用 Unicode 排序算法和默认 Unicode 排序元素表进行排序。它在所有编码中都可用。使用此排序需要 ICU 支持。（此排序与 ICU 根区域设置的行为相同；请参见
und-x-icu（对于 “未定义”）。）ucs_basic此排序按 Unicode 代码点排序。它仅适用于
UTF8编码。（此排序与UTF8编码中的 libc 区域设置规范C的行为相同。）
24.2.2.2. 预定义校对#
如果操作系统提供在单个程序中使用多个区域设置的支持（newlocale和相关函数），或者如果配置了对 ICU 的支持，那么当初始化数据库集群时，initdb会根据它在当时操作系统中找到的所有区域设置，用校对填充系统目录pg_collation。
要检查当前可用的区域设置，请使用查询SELECT * FROM pg_collation，或在psql中使用命令\dOS+。
24.2.2.2.1. libc 校对#
例如，操作系统可能提供名为de_DE.utf8的区域设置。然后，initdb会为编码UTF8创建名为de_DE.utf8的校对，该校对将LC_COLLATE和LC_CTYPE都设置为de_DE.utf8。它还将创建一个校对，其中.utf8标记从名称中剥离。因此，你还可以使用名称为de_DE的校对，这更便于编写，并且使名称不太依赖于编码。请注意，尽管如此，校对名称的初始集合还是依赖于平台的。
由libc提供的默认校对集合直接映射到操作系统中安装的区域设置，可以使用命令locale -a列出这些区域设置。如果需要具有不同LC_COLLATE和LC_CTYPE值的libc校对，或者如果在数据库系统初始化后在操作系统中安装了新的区域设置，那么可以使用CREATE COLLATION命令创建一个新的校对。还可以使用pg_import_system_collations()函数批量导入新的操作系统区域设置。
在任何特定数据库中，只有使用该数据库编码的校对才具有意义。pg_collation中的其他条目将被忽略。因此，剥离的校对名称（例如de_DE）可以在给定的数据库中被认为是唯一的，即使它在全局范围内不是唯一的。建议使用剥离的校对名称，因为如果你决定更改为其他数据库编码，它将使你需要更改的内容减少一项。但请注意，default、C和POSIX校对可以使用，而不管数据库编码如何。
PostgreSQL认为不同的校对对象是不兼容的，即使它们具有相同的属性。因此，例如，
SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;将绘制一个错误，即使C和POSIX校对具有相同的行为。因此，不建议混合使用剥离和未剥离的校对名称。
24.2.2.2.2. ICU 校对#
对于 ICU，枚举所有可能的语言环境名称是不明智的。ICU 使用特定命名系统来命名语言环境，但命名语言环境的方式比实际不同的语言环境多得多。initdb使用 ICU API 提取一组不同的语言环境来填充初始整理规则集。ICU 提供的整理规则在 SQL 环境中创建，其名称采用 BCP 47 语言标签格式，并附加一个“专用”扩展-x-icu，以将其与 libc 语言环境区分开来。
以下是一些可能创建的整理规则示例
de-x-icu#德语整理规则，默认变体
de-AT-x-icu#奥地利德语整理规则，默认变体
（例如，还有
de-DE-x-icu或de-CH-x-icu，但截至本文撰写之时，它们等效于de-x-icu。）und-x-icu（表示 “未定义”） #ICU “根” 整理规则。使用此规则可获得合理的与语言无关的排序顺序。
ICU 不支持某些（使用频率较低）编码。如果数据库编码是其中之一，则pg_collation中的 ICU 整理规则条目将被忽略。尝试使用其中一个条目将导致类似于“编码 "WIN874" 的整理规则 "de-x-icu" 不存在”的错误。
24.2.2.3. 创建新的整理规则对象#
如果标准和预定义整理规则不够用，用户可以使用 SQL 命令CREATE COLLATION创建自己的整理规则对象。
标准和预定义整理规则位于架构pg_catalog中，就像所有预定义对象一样。用户定义的整理规则应在用户架构中创建。这也确保它们由pg_dump保存。
24.2.2.3.1. libc 整理规则#
可以像这样创建新的 libc 整理规则
CREATE COLLATION german (provider = libc, locale = 'de_DE');此命令中locale子句可接受的确切值取决于操作系统。在类 Unix 系统上，命令locale -a将显示一个列表。
由于预定义的 libc 整理规则已在数据库实例初始化时包含操作系统中定义的所有整理规则，因此通常无需手动创建新整理规则。原因可能是需要不同的命名系统（在这种情况下，请参见第 24.2.2.3.3 节），或者操作系统已升级为提供新的语言环境定义（在这种情况下，请参见pg_import_system_collations()）。
24.2.2.3.2. ICU 排序规则#
ICU 排序规则可以像这样创建
CREATE COLLATION german (provider = icu, locale = 'de-DE');ICU 语言环境指定为 BCP 47语言标记，但也可以接受大多数 libc 风格的语言环境名称。如果可能，libc 风格的语言环境名称将转换为语言标记。
新的 ICU 排序规则可以通过在语言标记中包含排序规则属性来广泛自定义排序规则行为。有关详细信息和示例，请参阅第 24.2.3 节。
24.2.2.3.3. 复制排序规则#
命令CREATE COLLATION还可以用于从现有排序规则创建新的排序规则，这对于能够在应用程序中使用与操作系统无关的排序规则名称、创建兼容性名称或在更易读的名称下使用 ICU 提供的排序规则非常有用。例如
CREATE COLLATION german FROM "de_DE";
CREATE COLLATION french FROM "fr-x-icu";24.2.2.4. 非确定性排序规则#
排序规则要么是确定性的，要么是非确定性的。确定性排序规则使用确定性比较，这意味着它仅当字符串由相同的字节序列组成时才认为它们相等。非确定性比较可能会确定字符串相等，即使它们由不同的字节组成。典型情况包括不区分大小写的比较、不区分重音的比较以及不同 Unicode 规范形式中字符串的比较。由排序规则提供程序实际实现这种不区分的比较；确定性标志仅确定是否使用按字节比较来打破平局。有关术语的更多信息，另请参阅Unicode 技术标准 10。
要创建非确定性排序规则，请将属性deterministic = false指定给CREATE COLLATION，例如
CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);此示例将以非确定性方式使用标准 Unicode 校对。特别是，这将允许正确比较不同范式的字符串。更有趣的示例利用上面解释的 ICU 自定义功能。例如
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);所有标准和预定义校对都是确定性的，所有用户定义校对默认都是确定性的。虽然非确定性校对给出了更“正确”的行为，尤其是在考虑 Unicode 的全部功能及其许多特殊情况时，它们也有一些缺点。最重要的是，它们的使用会导致性能下降。特别注意，B 树不能对使用非确定性校对的索引使用重复数据删除。此外，某些操作无法与非确定性校对一起使用，例如模式匹配操作。因此，它们应该仅在特别需要它们的情况下使用。
提示
要处理不同 Unicode 规范化形式的文本，还可以使用函数/表达式normalize和is normalized来预处理或检查字符串，而不是使用非确定性校对。每种方法都有不同的权衡。
24.2.3. ICU 自定义校对#
ICU 允许通过定义具有作为语言标记一部分的校对设置的新校对来广泛控制校对行为。这些设置可以修改校对顺序以满足各种需求。例如
-- ignore differences in accents and case
CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true
SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true
-- upper case letters sort before lower case.
CREATE COLLATION upper_first (provider = icu, locale = 'und-u-kf-upper');
SELECT 'B' < 'b' COLLATE upper_first; -- true
-- treat digits numerically and ignore punctuation
CREATE COLLATION num_ignore_punct (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-kn');
SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- true
SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true第 24.2.3.2 节中描述了许多可用选项，或参阅第 24.2.3.5 节了解更多详细信息。
24.2.3.1. ICU 比较级别#
ICU 中两个字符串的比较（校对）由多级过程确定，其中文本特征被分组为“级别”。每个级别的处理由校对设置控制。较高的级别对应于更精细的文本特征。
表 24.1显示了在给定级别确定相等性时被认为是显着的文本特征差异。Unicode 字符U+2063是一个不可见的分割符，并且如表中所示，在低于identic的所有比较级别中都被忽略。
表 24.1. ICU 校对级别
| 级别 | 说明 | 'f' = 'f' | 'ab' = U&'a\2063b' | 'x-y' = 'x_y' | 'g' = 'G' | 'n' = 'ñ' | 'y' = 'z' |
|---|---|---|---|---|---|---|---|
| level1 | 基本字符 | true | true | true | true | true | false |
| level2 | 重音 | true | true | true | true | false | false |
| level3 | 大小写/变体 | true | true | true | false | false | false |
| level4 | 标点符号 | true | true | false | false | false | false |
| identic | 全部 | true | false | false | false | false | false |
在每个级别，即使完全关闭规范化，也会执行基本规范化。例如，'á'可能由代码点U&'\0061\0301'或单个代码点U&'\00E1'组成，即使在identic级别，这些序列也将被视为相等。要将代码点表示中的任何差异视为不同，请使用deterministic设置为true创建的排序规则。
24.2.3.1.1. 排序规则级别示例#
CREATE COLLATION level3 (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-level3');
CREATE COLLATION level4 (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-level4');
CREATE COLLATION identic (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-identic');
-- invisible separator ignored at all levels except identic
SELECT 'ab' = U&'a\2063b' COLLATE level4; -- true
SELECT 'ab' = U&'a\2063b' COLLATE identic; -- false
-- punctuation ignored at level3 but not at level 4
SELECT 'x-y' = 'x_y' COLLATE level3; -- true
SELECT 'x-y' = 'x_y' COLLATE level4; -- false24.2.3.2. ICU 语言环境的排序规则设置#
表 24.2显示了可用的排序规则设置，这些设置可以用作语言标记的一部分来定制排序规则。
表 24.2. ICU 排序规则设置
| 键 | 值 | 默认值 | 说明 |
|---|---|---|---|
co | emoji、phonebk、standard、... | standard | 排序规则类型。有关其他选项和详细信息，请参阅第 24.2.3.5 节。 |
ka | noignore、shifted | noignore | 如果设置为shifted，会导致某些字符（例如标点符号或空格）在比较中被忽略。键ks必须设置为level3或更低级别才能生效。设置键kv以控制忽略哪些字符类别。 |
kb | true、false | false | 级别 2 差异的反向比较。例如，语言环境und-u-kb将'àe'排在'aé'之前。 |
kc | true、false | false | 将大小写分成介于重音和其他级别 3 特征之间的“级别 2.5”。 如果设置为 |
kf | upper、lower、false | false | 如果设置为 upper，则大写字母在小写字母之前排序。如果设置为 lower，则小写字母在大写字母之前排序。如果设置为 false，则排序取决于区域设置规则。 |
kn | true、false | false | 如果设置为 true，则字符串中的数字将被视为单个数字值，而不是一系列数字。例如，'id-45' 在 'id-123' 之前排序。 |
kk | true、false | false | 启用完全规范化；可能会影响性能。即使设置为 在某些情况下，完全规范化很重要，例如当对单个字符应用多个重音符号时。例如，代码点序列 |
kr | space、punct、symbol、currency、digit、script-id | 设置为一个或多个有效值，或任何 BCP 47 重新定义字符类的排序；属于列表中较早类的那些字符在属于列表中较晚类的字符之前排序。例如，值 | |
ks | level1、level2、level3、level4、identic | level3 | 确定相等性时的敏感度（或“强度”），其中 level1 对差异最不敏感，而 identic 对差异最敏感。有关详细信息，请参见 表 24.1。 |
kv | space、punct、symbol、currency | punct | 在级别 3 比较期间忽略的字符类别。设置为较晚的值包括较早的值；例如，symbol 还包括 punct 和 space 中要忽略的字符。必须将键 ka 设置为 shifted，并将键 ks 设置为 level3 或更低级别才能生效。 |
默认值可能取决于区域设置。上表并非旨在完整无缺。有关其他选项和详细信息，请参阅第 24.2.3.5 节。
注意
对于许多排序设置，您必须使用deterministic设置为false创建排序，才能让设置产生所需的效果（请参阅第 24.2.2.4 节）。此外，某些设置只有在键ka设置为shifted时才生效（请参阅表 24.2）。
24.2.3.3. 排序设置示例#
CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');#具有电话簿排序类型的德语排序
CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');#具有表情符号排序类型的根排序，根据 Unicode 技术标准 #51
CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');#在拉丁字母之前对希腊字母进行排序。（默认情况下，拉丁字母在希腊字母之前。）
CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');#在大写字母之前对小写字母进行排序。（默认情况下，小写字母在前。）
CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');#组合上述两个选项。
24.2.3.4. ICU 定制规则#
如果上述整理设置提供的选项不够用，可以使用定制规则来更改整理元素的顺序，其语法详见https://unicode-org.github.io/icu/userguide/collation/customization/。
这个小示例基于根区域设置创建一个整理，并带有定制规则
CREATE COLLATION custom (provider = icu, locale = 'und', rules = '&V << w <<< W');使用此规则，字母“W”排在“V”之后，但作为次要差异对待，类似于重音。此类规则包含在某些语言的区域设置定义中。（当然，如果区域设置定义中已包含所需的规则，则无需再次明确指定。）
下面是一个更复杂的示例。以下语句设置一个名为ebcdic的整理，并带有用于按 EBCDIC 编码顺序整理美国 ASCII 字符的规则。
CREATE COLLATION ebcdic (provider = icu, locale = 'und',
rules = $$
& ' ' < '.' < '<' < '(' < '+' < \|
< '&' < '!' < '$' < '*' < ')' < ';'
< '-' < '/' < ',' < '%' < '_' < '>' < '?'
< '`' < ':' < '#' < '@' < \' < '=' < '"'
<*a-r < '~' <*s-z < '^' < '[' < ']'
< '{' <*A-I < '}' <*J-R < '\' <*S-Z <*0-9
$$);
SELECT c
FROM (VALUES ('a'), ('b'), ('A'), ('B'), ('1'), ('2'), ('!'), ('^')) AS x(c)
ORDER BY c COLLATE ebcdic;
c
---
!
a
b
^
A
B
1
224.2.3.5. ICU 外部引用#
本节（第 24.2.3 节）只是对 ICU 行为和语言标记的简要概述。有关技术详细信息、其他选项和新行为，请参阅以下文档