Docs4dev
Docs
postgre-sql / 10.7 / reference / collation.html

23.2. 整理支持

归类功能允许按列甚至按操作指定数据的排序 Sequences 和字符分类行为。这减轻了数据库创建后不能更改LC_COLLATE和LC_CTYPE设置的限制。

23.2.1. Concepts

从概念上讲，可归类数据类型的每个表达式都有一个归类。 (内置的可整理数据类型是text，varchar和char.用户定义的基本类型也可以标记为可整理的，当然，可整理数据类型上的域也是可整理的.)如果表达式是列引用，则表达式的排序规则是列的已定义排序规则。如果表达式是常量，则排序规则是常量数据类型的默认排序规则。如下所述，更复杂的表达式的排序规则是从其 Importing 的排序规则中得出的。

表达式的排序规则可以是“默认”排序规则，这表示为数据库定义的语言环境设置。表达式的排序规则也可能不确定。在这种情况下，需要知道排序规则的 Order 操作和其他操作将失败。

当数据库系统必须执行排序或字符分类时，它将使用 Importing 表达式的排序规则。例如，使用ORDER BY子句以及<之类的函数或运算符会发生这种情况。申请ORDER BY子句的排序规则只是排序键的排序规则。如下所述，从函数派生出用于申请函数或运算符的排序规则。除比较运算符外，在小写和大写字母之间进行转换的函数(例如lower，upper和initcap)还考虑了归类。通过模式匹配运算符；以及to_char和相关功能。

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

表达式的排序规则派生可以是隐式或显式的。当一个表达式中出现多个不同的排序规则时，这种区别会影响排序规则的组合方式。当使用COLLATE子句时，会进行显式的排序规则推导。所有其他归类推导都是隐式的。当需要合并多个排序规则时(例如在函数调用中)，将使用以下规则：

  • 如果任何 Importing 表达式具有显式排序规则派生，则 Importing 表达式之间的所有显式派生排序规则必须相同，否则会引发错误。如果存在任何显式派生的排序规则，则这是排序规则组合的结果。

  • 否则，所有 Importing 表达式必须具有相同的隐式归类派生或默认归类。如果存在任何非默认排序规则，则这是排序规则组合的结果。否则，结果是默认排序规则。

  • 如果 Importing 表达式之间存在冲突的非默认隐式归类，则认为该组合具有不确定的归类。除非正在调用的特定功能需要了解应应用的排序规则，否则这不是错误条件。如果是这样，则会在运行时引发错误。

例如，考虑以下表定义：

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

Then in

SELECT a < 'foo' FROM test1;

<比较是根据de_DE规则执行的，因为该表达式将隐式派生的排序规则与默认排序规则结合在一起。但在

SELECT a < ('foo' COLLATE "fr_FR") FROM test1;

比较是使用fr_FR规则执行的，因为显式排序规则推导会覆盖隐式规则。此外，给定

SELECT a < b FROM test1;

解析器无法确定要应用哪个排序规则，因为a和b列具有冲突的隐式排序规则。由于<运算符确实需要知道使用哪个排序规则，因此将导致错误。可以通过将显式排序规则说明符附加到任一 Importing 表达式来解决该错误，因此：

SELECT a < b COLLATE "de_DE" FROM test1;

or equivalently

SELECT a COLLATE "de_DE" < b FROM test1;

另一方面，结构相似的情况

SELECT a || b FROM test1;

不会导致错误，因为||运算符并不关心排序规则：无论排序规则如何，其结果都是相同的。

如果函数或运算符提供可排序数据类型的结果，则分配给函数或运算符组合 Importing 表达式的排序规则也被认为适用于函数或运算符的结果。所以，在

SELECT * FROM test1 ORDER BY a || 'foo';

将根据de_DE规则进行 Order。但是这个查询：

SELECT * FROM test1 ORDER BY a || b;

会导致错误，因为即使||运算符不需要知道排序规则，但ORDER BY子句也可以。和以前一样，可以使用显式的排序规则说明符解决冲突：

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

23 .2.2. Management 归类

排序规则是一个 SQL 模式对象，它将 SQL 名称 Map 到 os 中安装的库提供的语言环境。排序规则定义具有* provider *，用于指定哪个库提供区域设置数据。一个标准的提供程序名称是libc，它使用 osC 库提供的语言环境。这些是 os 提供的大多数工具使用的语言环境。另一个提供程序是icu，它使用外部 ICU 库。仅当在构建 PostgreSQL 时配置了对 ICU 的支持时，才能使用 ICU 语言环境。

setlocale()系统库调用接受的libc提供的排序规则对象 Map 到LC_COLLATE和LC_CTYPE设置的组合。 (顾名思义，排序规则的主要目的是设置LC_COLLATE，它控制排序 Sequences.但是实际上，很少有LC_CTYPE设置不同于LC_COLLATE的设置，因此更方便地收集它们而不是创建用于为每个表达式设置LC_CTYPE的另一种基础结构.)此外，libc归类与字符集编码(请参见Section 23.3)绑定在一起。对于不同的编码，可能存在相同的归类名称。

icu提供的排序规则对象 Map 到 ICU 库提供的命名排序规则。 ICU 不支持单独的“整理”和“ ctype”设置，因此它们始终相同。另外，ICU 归类与编码无关，因此数据库中始终只有一个给定名称的 ICU 归类。

23 .2.2.1. 标准归类

在所有平台上，都可以使用名为default，C和POSIX的排序规则。根据 os 支持，可能会提供其他归类。 default归类选择在数据库创建时指定的LC_COLLATE和LC_CTYPE值。 C和POSIX归类均指定“传统 C”行为，其中只有 ASCII 字母“ A”至“ Z”被视为字母，并且严格按照字符代码字节值进行排序。

此外，SQL 标准归类名称ucs_basic可用于编码UTF8。它等效于C并按 Unicode 代码点排序。

23 .2.2.2. sched 义归类

如果 os 支持在单个程序中使用多个语言环境(newlocale和相关功能)，或者配置了对 ICU 的支持，则在初始化数据库集群时，initdb将使用基于所有语言环境的归类填充系统目录pg_collation它可以在当时的 os 中找到。

要检查当前可用的语言环境，请在 psql 中使用查询SELECT * FROM pg_collation或命令\dOS+。

23 .2.2.2.1. libc 归类

例如，os 可能提供名为de_DE.utf8的语言环境。然后initdb将创建名为de_DE.utf8的归类，以对LC_COLLATE和LC_CTYPE都设置为de_DE.utf8的UTF8进行编码。还将使用.utf8标记创建一个归类名称的排序规则。因此，您也可以在名称de_DE下使用归类，它简化了编写工作，并使名称不依赖于编码。但是请注意，归类名称的初始集合是平台相关的。

libc提供的默认排序规则集直接 Map 到 os 中安装的语言环境，可以使用命令locale -a列出。如果需要libc排序规则的LC_COLLATE和LC_CTYPE具有不同的值，或者如果在初始化数据库系统之后在 os 中安装了新的语言环境，则可以使用CREATE COLLATION命令创建新的排序规则。也可以使用pg_import_system_collations()函数批量导入新的 os 区域设置。

在任何特定数据库中，只有使用该数据库编码的归类才有意义。 pg_collation中的其他条目将被忽略。因此，即使在 Global 范围内，剥离的归类名称(例如de_DE)在给定的数据库中也可以视为唯一。建议使用剥离的排序规则名称，因为如果您决定更改为另一种数据库编码，则将减少您需要更改的内容。但是请注意，可以使用default，C和POSIX归类，无论数据库编码如何。

PostgreSQL 认为不同的归类对象即使具有相同的属性也不兼容。因此，例如

SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;

即使C和POSIX归类具有相同的行为，也会产生错误。因此，不建议混合使用剥离和非剥离排序规则名称。

23 .2.2.2.2. ICU 归类

使用 ICU，枚举所有可能的语言环境名称是不明智的。 ICU 对语言环境使用特定的命名系统，但是命名语言环境的方法比实际上存在不同的语言环境的方法更多。 initdb使用 ICU API 提取了一组不同的语言环境，以填充初始的排序规则集。由 ICU 提供的排序规则是在 SQL 环境中创建的，其名称采用 BCP 47 语言标记格式，并附加了“专用”extensions-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“根”整理。使用它来获得合理的与语言无关的排序 Sequences。

ICU 不支持某些(不太常用的)编码。如果数据库编码是其中一种，则pg_collation中的 ICU 整理条目将被忽略。尝试使用“将不存在用于编码“ WIN874”的归类“ de-x-icu””的错误。

23 .2.2.3. 创建新的归类对象

如果标准归类和 sched 义归类不足，则用户可以使用 SQL 命令CREATE COLLATION创建自己的归类对象。

像所有 sched 义对象一样，标准归类和 sched 义归类在模式pg_catalog中。应在用户架构中创建用户定义的归类。这也可以确保它们被pg_dump保存。

23 .2.2.3.1. libc 归类

可以这样创建新的 libc 归类：

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

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

由于在初始化数据库实例时，sched 义的 libc 排序规则已经包含 os 中定义的所有排序规则，因此通常无需手动创建新的排序规则。原因可能是需要不同的命名系统(在这种情况下，还请参见Section 23.2.2.3.3)，或者是否已升级 os 以提供新的语言环境定义(在这种情况下，还请参见pg_import_system_collations())。

23 .2.2.3.2. ICU 归类

ICU 允许在initdb预加载的基本语言国家/地区之外自定义归类。鼓励用户定义自己的归类对象，这些对象使用这些功能来使排序行为符合其要求。有关 ICU 语言环境命名的信息，请参见http://userguide.icu-project.org/locale和http://userguide.icu-project.org/collation/api。可接受的名称和属性集取决于特定的 ICU 版本。

这里有些例子：

  • CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');
    CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook');

    • 德语排序规则与电话簿排序规则类型

第一个示例根据 BCP 47 使用“语言标记”选择 ICU 语言环境。第二个示例使用传统的 ICU 特定语言环境语法。首选使用第一种样式，但是较旧的 ICU 版本不支持该样式。

请注意，您可以在 SQL 环境中随意命名命名对象。在此示例中，我们遵循 sched 义归类所使用的命名样式，该命名样式又遵循 BCP 47，但是用户定义归类不是必需的。

  • CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');
    CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji');

    • 根据 Unicode 技术标准#51，具有 Emoji 排序规则类型的根排序规则

观察在传统的 ICU 语言环境命名系统中，如何通过空字符串选择根语言环境。

  • CREATE COLLATION digitslast (provider = icu, locale = 'en-u-kr-latn-digit');
    CREATE COLLATION digitslast (provider = icu, locale = 'en@colReorder=latn-digit');

    • 在拉丁字母之后对数字进行排序。 (默认值为字母前的数字.)

  • CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');
    CREATE COLLATION upperfirst (provider = icu, locale = 'en@colCaseFirst=upper');

    • 将大写字母排序在小写字母之前。 (默认为小写字母优先.)

  • CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-latn-digit');
    CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=latn-digit');

    • 结合了以上两个选项。

  • CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
    CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

    • 数字排序按数字值对数字序列进行排序，例如：A-21 <A-123(也称为自然排序)。

有关详情，请参见Unicode 技术标准#35和BCP 47。可以在CLDR repository中找到可能的排序规则类型的列表(co子标记)。 ICU 语言环境浏览器可用于检查特定语言环境定义的详细信息。使用k*子标记的示例至少需要 ICU 版本 54.

请注意，尽管该系统允许创建“忽略大小写”或“忽略重音符号”或类似内容的排序规则(使用ks键)，但 PostgreSQL 目前不允许此类排序规则以不区分大小写或不区分重音的方式起作用。根据排序规则比较相等但按字节排序不相等的任何字符串将根据其字节值进行排序。

Note

按照设计，ICU 将使用其文档中介绍的后备过程将几乎任何字符串都用作语言环境名称，并将其与它可以提供的最接近的语言环境匹配。因此，如果整理规范是使用给定 ICU 安装实际上不支持的功能组成的，则不会有直接反馈。因此，建议创建应用程序级测试用例，以检查归类定义是否满足其要求。

23 .2.2.3.3. 复制归类

命令CREATE COLLATION还可以用于从现有排序规则创建新的排序规则，这对于在应用程序中使用独立于 os 的排序规则名称，创建兼容性名称或在 ICU 上使用 ICU 提供的排序规则很有用。可读的名称。例如：

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