Postgresql 中文操作指南

24.2. Collation Support #

排序功能允许按列甚至按操作指定数据的排序顺序和字符分类行为。这缓解了以下限制：在创建数据库后，不能更改其 LC_COLLATE 和 LC_CTYPE 设置。

24.2.1. Concepts #

从概念上来讲，可并列数据类型的所有表达式都具有排序规则。（内置的可并列数据类型有 text 、 varchar 和 char 。用户定义的基本类型也可以标记为可并列，当然，可并列数据类型上的 [role="bare"]glossary.html#GLOSSARY-DOMAIN domain 也是可并列的。）如果表达式是列引用，则表达式的排序规则是该列已定义的排序规则。如果表达式是常量，则该排序规则是常量的数据类型的默认排序规则。如下所述，更复杂的表达式的排序规则来自其输入的排序规则。

表达式的排序规则可以是“默认”排序规则，这意味着针对数据库定义的区域设置。表达式的排序规则也可能是不确定的。在这些情况下，排序操作和其他需要知道排序规则的操作将失败。

当数据库系统必须执行排序或字符分类时，它将使用输入表达式的排序规则。例如，这发生在 ORDER BY 子句和函数或运算符调用（如 <）中。要应用于 ORDER BY 子句的排序规则仅仅是排序键的排序规则。要应用于函数或运算符调用的排序规则源自参数，如下所述。除了比较运算符外，排序规则还被用于在大小写字母之间转换的函数（如 lower、upper 和 initcap）、模式匹配运算符以及 to_char 和相关函数。

对于函数或运算符调用，通过检查参数排序规则而派生的排序规则在运行时用于执行指定的操作。如果函数或运算符调用的结果是可排序的数据类型，则排序规则在解析时还用作函数或运算符表达式的定义的排序规则，以防有需要了解其排序规则的周围表达式。

表达式的 collation derivation 可以是隐式的或显式的。当一个表达式中出现多个不同的排序规则时，这种区别会影响排序规则的组合方式。当使用 COLLATE 子句时，就会发生显式的排序规则派生；所有其他排序规则派生都是隐式的。当需要组合多个排序规则时，例如在函数调用中，会使用以下规则：

例如，考虑此表定义：

CREATE TABLE test1 (
    a text COLLATE "de_DE",
    b text COLLATE "es_ES",
    ...
);

那么在

SELECT a < 'foo' FROM test1;

@{16} 比较是根据 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. Managing Collations #

排序规则是将 SQL 名称映射到操作系统中安装的库提供的区域设置的 SQL 架构对象。排序规则定义有一个 provider，用于指定哪个库提供区域设置数据。一个标准提供程序名称是 libc，它使用操作系统 C 库提供的区域设置。这些区域设置是操作系统提供的大多数工具使用的区域设置。另一个提供程序是 icu，它使用外部 ICU 库。只有在构建 PostgreSQL 时配置了对 ICU 的支持时，才能使用 ICU 区域设置。

libc 提供的并列对象映射到 LC_COLLATE 和 LC_CTYPE 设置的组合，为 setlocale() 系统库函数调用所接受。（顾名思义，并列的主要目的是设置 LC_COLLATE，它控制排序顺序。但是在实践中，很少需要设置与 LC_COLLATE 不同的 LC_CTYPE ，因此将这些合并到一个概念中比为每个表达式创建另一个设置 LC_CTYPE 的基础架构更为方便。）此外，libc 并列与字符集编码相关（参见 Section 24.3）。相同并列名称可能存在于不同的编码中。

icu 提供的对比对象会映射到 ICU 库提供的已命名对比器。ICU 不支持单独的“对比”和“字符类型”设置，因此它们始终相同。此外，ICU 对比与编码无关，所以数据库中给定名称的 ICU 对比始终只有一个。

24.2.2.1. Standard Collations #

在所有平台上，已命名的 default、C 和 POSIX 对比均可用。根据操作系统支持也可使用其他对比。default 对比会在数据库创建时选择 LC_COLLATE 和 LC_CTYPE 指定的值。C 和 POSIX 对比都指定了“传统 C”行为，其中只有 ASCII 字母“A”到“Z”被视为字母，排序严格按照字符代码字节值进行。

Note

C 和 POSIX 区域设置的行为可能会有所不同，具体取决于数据库编码。

此外，还提供了两个 SQL 标准对比名称：

  • unicode

    • 该排序规则使用带有默认 Unicode 排序元素表的 Unicode 排序算法进行排序。在所有编码中都可以使用。必须具备 ICU 支持才能使用此排序规则。（此排序规则与 ICU 根语言环境的行为相同；参见 und-x-icu (for “undefined”) 。）

  • ucs_basic

    • 此对比按 Unicode 代码点排序。它仅可用于 UTF8 编码。（此对比具有与 libc 区域设置 C 在 UTF8 编码中的相同行为。）

24.2.2.2. Predefined Collations #

如果操作系统提供在单个程序（newlocale 和相关函数）内使用多个区域设置的支持，或者如果 ICU 支持已配置，那么在初始化数据库集群时，initdb 将使用操作系统中找到的所有区域设置填充系统目录 pg_collation 中的对比。

要检查当前可用的区域设置，请使用查询 SELECT * FROM pg_collation 或 psql 中的命令 \dOS+。

例如，操作系统可能提供了名为 de_DE.utf8 的区域设置。initdb 然后将创建针对编码为 UTF8 的对比，名为 de_DE.utf8，且 LC_COLLATE 和 LC_CTYPE 均设置为 de_DE.utf8。它还会从名称中去掉 .utf8 标签创建对比。因此，您也可以使用名称 de_DE 中的对比，它书写起来不那么繁琐，并且使名称对编码的依赖性降低。请注意，尽管如此，对比名称的初始集合仍取决于平台。

libc 提供的默认排序规则集直接映射到操作系统中安装的语言环境，可以使用 locale -a 命令列出这些语言环境。如果需要 libc 排序规则（其为 LC_COLLATE 和 LC_CTYPE 提供了不同的值），或者在数据库系统初始化后操作系统中安装了新的语言环境，则可以使用 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 对比具有相同行为。因此，不建议混合使用剥离的对比名称和未剥离的对比名称。

使用 ICU，枚举所有可能的区域设置名称是不明智的。ICU 对区域设置使用特定的命名系统，但为区域设置命名的方式比实际不同的区域设置要多得多。initdb 使用 ICU API 提取一组不同的区域设置，以填充对比的初始集合。ICU 提供的对比在 SQL 环境中创建，名称采用 BCP 47 语言标签格式，并附加“专用”扩展 -x-icu，以将它们与 libc 区域设置区分开来。

以下是一些可能创建的对照示例：

  • de-x-icu #

    • German collation, default variant

  • de-AT-x-icu #

    • 奥地利德语对照，默认变体

    • （据说还有 de-DE-x-icu 或 de-CH-x-icu，但截至本文撰写时，它们等同于 de-x-icu。）

  • und-x-icu (for “undefined”) #

    • ICU“根”对照。使用它来获取合理的语言无关的排序顺序。

某些（不常使用）的编码不受 ICU 支持。当数据库编码是其中之一时，pg_collation 中的 ICU 排序条目将被忽略。尝试使用该条目会导致类似于 "排序 de-x-icu 对于编码 WIN874 不存在" 的错误。

24.2.2.3. Creating New Collation Objects #

如果标准和预定义排序规则不够用，则用户可以使用 SQL 命令 CREATE COLLATION 创建自己的排序规则对象。

标准和预定义的排序位于 pg_catalog 架构中，如同所有预定义对象那样。用户定义的排序应在用户架构中创建。这还可确保这些排序由 pg_dump 保存。

可以按照如下方式创建新的 libc 排序：

CREATE COLLATION german (provider = libc, locale = 'de_DE');

此命令中 locale 子句可接受的确切值取决于操作系统。在类似 Unix 的系统中，locale -a 会显示一个列表。

由于预定义的 libc 排序规则已经在数据库实例初始化时包含了操作系统中定义的所有排序规则，因此通常不需要手动创建新的排序规则。原因可能是希望使用不同的命名系统（在这种情况下，请参见 Section 24.2.2.3.3 ），或者操作系统已升级为提供新的语言环境定义（在这种情况下，请参见 pg_import_system_collations() ）。

可以按照如下方式创建 ICU 排序：

CREATE COLLATION german (provider = icu, locale = 'de-DE');

ICU 语言区域设置指定为 BCP 47 Language Tag，但也可以接受大多数 libc 风格语言区域设置名称。尽可能，lib-c 风格语言区域设置名称将转换为语言标记。

通过在语言标记中包含并列属性，新的 ICU 并列可以广泛自定义并列行为。有关详情和示例，请参见 Section 24.2.3。

命令 CREATE COLLATION 还可以用于从现有排序规则创建一个新的排序规则，这对于在应用程序中使用与操作系统无关的排序规则名称、创建兼容性名称或在更易读的名称下使用 ICU 提供的排序规则非常有用。例如：

CREATE COLLATION german FROM "de_DE";
CREATE COLLATION french FROM "fr-x-icu";

24.2.2.4. Nondeterministic Collations #

并列要么是 deterministic 要么是 nondeterministic 。确定性的并列使用确定的比较，这意味着只有当比较的字符串由相同的字节序列组成时，它才将字符串视为相同。非确定性比较即使由不同的字节组成，也可能将字符串确定为相同。典型的情况包括不区分大小写的比较、不区分重音的比较以及比较不同 Unicode 标准形式的字符串。由并列提供程序实际实现此类不敏感的比较；这个确定性的标记仅确定是否要使用按字节比较来打破平局。有关此术语的更多信息，请参阅 Unicode Technical Standard 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 树无法在使用非确定性排序的索引中使用重复数据消除。此外，某些操作无法与非确定性排序一起使用，例如模式匹配操作。因此，仅应在特别需要时使用它们。

Tip

为了处理不同 Unicode 规范形式的文本，也可以使用函数/表达式 normalize 和 is normalized 来预处理或检查字符串，而不是使用非确定性排序。每种方法都有不同的权衡取舍。

24.2.3. ICU Custom Collations #

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

Section 24.2.3.2中介绍了许多可用选项，或查看 Section 24.2.3.5 了解更多详情。

24.2.3.1. ICU Comparison Levels #

ICU 中两个字符串（并列）的比较由多级进程决定，其中文本特征分为“级别”。每个级别的处理由 collation settings 控制。较高级别对应于更精细的文本特征。

Table 24.1 展示了在给定级别确定相等性时哪些文本特征差异被视为显著的。Unicode 字符 U+2063 是一个不可见的分割符，如表中所示，在低于 identic 的所有比较级别中都会忽略它。

Table 24.1. ICU Collation Levels

Level

Description

'f' = 'f'

'ab' = U&'a\2063b'

'x-y' = 'x_y'

'g' = 'G'

'n' = 'ñ'

'y' = 'z'

level1

Base Character

true

true

true

true

true

false

level2

Accents

true

true

true

true

false

false

level3

Case/Variants

true

true

true

false

false

false

level4

Punctuation

true

true

false

false

false

false

identic

All

true

false

false

false

false

false

在各等级中，即使在完全规范化关闭的情况下，也会执行基本规范化。例如，'á' 可能由代码点 U&'\0061\0301' 或单个代码点 U&'\00E1' 组成，并且即使在 identic 等级中，这些序列也会被视为相等。要将代码点表示中的任何差异视为不同，请使用 deterministic 设置为 true 创建的排序规则。

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; -- false

24.2.3.2. Collation Settings for an ICU Locale #

Table 24.2 展示了可用的并列设置，这些设置可以用作语言标记的一部分来自定义并列。

Table 24.2. ICU Collation Settings

Key

Values

Default

Description

co

emoji, phonebk, standard, …​

standard

排序规则类型。有关其他选项和详细信息，请参见 Section 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 级”。如果设置为 true 且 ks 设置为 level1 ，则会忽略重音但考虑大小写。

kf

upper, lower, false

false

如果设置为 upper ，则大写字母按字母顺序排在大写字母之前。如果设置为 lower ，则小写字母按字母顺序排在大写字母之前。如果设置为 false ，则按语言环境规则排序。

kn

true, false

false

如果设置为 true ，则字符串中的数字被视为一个单独的数字值，而不是数字序列。例如， 'id-45' 按字母顺序排在 'id-123' 之前。

kk

true, false

false

启用完全规范化；可能会影响性能。即使设置为 false ，也会执行基本规范化。需要完全规范化的语言的语言环境通常默认启用它。在某些情况下，完全规范化非常重要，例如对单个字符应用多个重音时。例如，代码点序列 U&amp;'\0065\0323\0302' 和 U&amp;'\0065\0302\0323' 表示一个 e ，其按不同的顺序应用了抑扬符和下圆点重音。打开完全规范化后，这些代码点序列将被视为相等的；否则，它们是不相等的。

kr

space 、 punct 、 symbol 、 currency 、 digit 、 script-id

设置为一个或多个有效值，或任何 BCP 47 script-id ，例如 latn （“拉丁语”）或 grek （“希腊语”）。多个值用“ - ”分隔。重新定义字符类的顺序；属于列表中较早类的字符按字母顺序排在属于列表中较晚类的字符之前。例如，值 digit-currency-space （作为语言标签 und-u-kr-digit-currency-space 的一部分）将标点符号按字母顺序排在数字和空格之前。

ks

level1 、 level2 、 level3 、 level4 、 identic

level3

在确定等效性时灵敏度（或“强度”），其中 level1 对差异最不敏感，而 identic 对差异最敏感。有关详细信息，请参见 Table 24.1 。

kv

space, punct, symbol, currency

punct

在第 3 级比较期间忽略的字符类别。设置后值包括较早的值；例如， symbol 也在要忽略的字符中包含 punct 和 space 。密钥 ka 必须设置为 shifted ，而密钥 ks 必须设置为 level3 或更低的值才能生效。

默认设置可能取决于语言区域设置。上表并非旨在做到全面。有关其他选项和详细信息，请参阅 Section 24.2.3.5。

Note

对于许多校对设置，您必须使用_false_设置_deterministic_来创建校对设置，才能达到理想效果（见 Section 24.2.2.4）。此外，只有当密钥_ka_设置为_shifted_时，某些设置才能生效（见 Table 24.2）。

24.2.3.3. Collation Settings Examples #

  • 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'); #

    • 具有 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 Tailoring Rules #

如果上面显示的并列设置所提供的选项不足以使用，则可以使用调整规则来更改并列元素的顺序，其语法详细介绍在 https://unicode-org.github.io/icu/userguide/collation/customization/ 中。

这个小示例创建了一个基于根区域设置的排序规则，并附加了定制规则：

CREATE COLLATION custom (provider = icu, locale = 'und', rules = '&V << w <<< W');

使用此规则，字母“W”按顺序排在字母“V”之后，但被视为次要差异，类似于重音。此类规则包含在某些语言的区域设置定义中。（当然，如果区域设置定义已经包含所需的规则，则不必再明确指定这些规则。）

下面是一个更为复杂的示例。以下语句设置了一个名为 ebcdic 的整理规则，其规则是按照 EBCDIC 编码对 US-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
 2

24.2.3.5. External References for ICU #

本部分 ( Section 24.2.3) 仅简要概述了 ICU 行为和语言标记。有关技术细节、其他选项和新行为，请参阅以下文档：