PG中的排序规则

排序规则支持

排序规则特性允许指定每一列甚至每一个操作的数据的排序顺序和字符分类行为。这放松了数据库的LC_COLLATE和LC_CTYPE设置自创建以后就不能更改这一限制。

1. 概念

在概念上，一种可排序数据类型的每一种表达式都有一个排序规则（内建的可排序数据类型是text、varchar和char。用户定义的基础类型也可以被标记为可排序的，并且在一种可排序数据类型上的域也是可排序的）。如果该表达式是一个列引用，该表达式的排序规则就是列所定义的排序规则。如果该表达式是一个常量，排序规则就是该常量数据类型的默认排序规则。更复杂表达式的排序规则根据其输入的排序规则得来，如下所述：

一个表达式的排序规则可以是“默认”排序规则，它表示数据库的区域设置。一个表达式的排序规则也可能是不确定的。在这种情况下，排序操作和其他需要知道排序规则的操作会失败。

当数据库系统必须要执行一次排序或者字符分类时，它使用输入表达式的排序规则。这会在使用例如ORDER BY子句以及函数或操作符调用（如<）时发生。应用于ORDER BY子句的排序规则就是排序键的排序规则。应用于函数或操作符调用的排序规则从它们的参数得来，具体如下文所述。除比较操作符之外，在大小写字母之间转换的函数会考虑排序规则，例如lower、upper和initcap。模式匹配操作符和to_char及相关函数也会考虑排序规则。

对于一个函数或操作符调用，其排序规则通过检查在执行指定操作时参数的排序规则来获得。如果该函数或操作符调用的结果是一种可排序的数据类型，万一有外围表达式要求函数或操作符表达式的排序规则，在解析时结果的排序规则也会被用作函数或操作符表达式的排序规则。

一个表达式的排序规则派生可以是显式或隐式。该区别会影响多个不同的排序规则出现在同一个表达式中时如何组合它们。当使用一个COLLATE子句时，将发生显式排序规则派生。所有其他排序规则派生都是隐式的。当多个排序规则需要被组合时（例如在一个函数调用中），将使用下面的规则：

1. 如果任何一个输入表达式具有一个显式排序规则派生，则在输入表达式之间的所有显式派生的排序规则必须相同，否则将产生一个错误。如果任何一个显式派生的排序规则存在，它就是排序规则组合的结果。
2. 否则，所有输入表达式必须具有相同的隐式排序规则派生或默认排序规则。如果任何一个非默认排序规则存在，它就是排序规则组合的结果。否则，结果是默认排序规则。
3. 如果在输入表达式之间存在冲突的非默认隐式排序规则，则组合被认为是具有不确定排序规则。这并非一种错误情况，除非被调用的特定函数要求提供排序规则的知识。如果它确实这样做，运行时将发生一个错误。

例如，考虑这个表定义：

然后在

中，<比较被根据de_DE规则执行，因为表达式组合了一个隐式派生的排序规则和默认排序规则。但是在

中，比较被使用fr_FR规则执行，因为显式排序规则派生重载了隐式排序规则。更进一步，给定

解析器不能确定要应用哪个排序规则，因为a列和b列具有冲突的隐式排序规则。由于<操作符不需要知道到底使用哪一个排序规则，这将会导致一个错误。该错误可以通过在一个输入表达式上附加一个显式排序规则说明符来解决，因此：

或者等效的

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

不会导致一个错误，因为||操作符不关心排序规则：不管排序规则怎样它的结果都相同。

如果一个函数或操作符发送一个具有可排序数据类型的结果，分配给该函数或操作符的组合输入表达式的排序规则也被考虑应用在函数或操作符的结果。因此，在

中排序将根据de_DE规则完成。但这个查询：

会导致一个错误，因为即使||操作符不需要知道排序规则，但ORDER BY子句需要。按照以前，冲突可以通过使用一个显式排序规则说明符来解决：

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排序规则是和一个字符集编码（见第 23.3 节） 绑定在一起的。相同的排序规则名字可能存在于不同的编码中。

由icu提供的排序规则对象映射到由ICU库提供的指定整理器。 ICU不支持单独的“collate”和“ctype”设置， 所以它们总是相同的。此外，ICU排序规则与编码无关， 因此在数据库中总是只有一个给定名称的ICU排序规则。

2.1. 标准的排序规则

在所有的平台上，名为default、C和POSIX的排序规则都可用。附加的排序规则是否可用取决于操作系统的支持。default排序规则选择在数据库创建时指定的LC_COLLATE和LC_CTYPE值。C和POSIX排序规则都指定了“传统的C”行为，在其中只有ASCII字母“A”到“Z”被视为字母，并且排序严格地按照字符编码的字节值完成。

此外，SQL标准排序规则名称ucs_basic可用于编码UTF8。 它相当于C，并按Unicode代码点排序。

2.2. 预定义的排序规则

如果操作系统支持在一个程序中使用多个区域（newlocale和相关函数）， 或者配置了ICU支持，那么在一个数据集簇被初始化时，initdb 将以它在操作系统中能找到的所有区域为基础在系统目录pg_collation 中填充排序规则。

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

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列出。如果所需的libc 排序规则与LC_COLLATE和LC_CTYPE的值不同， 或者在数据库系统初始化之后， 操作系统中安装了新的语言环境，可以使用CREATE COLLATION 命令创建新的排序规则。新的操作系统语言环境也可以使用 pg_import_system_collations() 函数集中导入。

在任何特定的数据库中，只有使用数据库编码的排序规则是令人感兴趣的。其他pg_collation中的项会被忽略。因此，一个如de_DE的被剥离的排序规则名在一个给定数据库中可以被认为是唯一的，即使它在全局上并不唯一。我们推荐使用被剥离的排序规则名，因为在你决定要更改到另一个数据库编码时需要做的事情更少。但是要注意default、C和POSIX排序规则在使用时可以不考虑数据库编码。

PostgreSQL在碰到具有相同属性的不同排序规则对象时会认为它们是不兼容的。因此对于例子：

将会得到一个错误，即使C和POSIX排序规则具有相同的行为。因此，我们不推荐混合使用被剥离的和非被剥离的排序规则名。

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 (for “undefined”)

  ICU “root” 排序规则。 使用它获取合理的语言无关的排序顺序

一些（不常用的）编码不受ICU支持。当数据库编码是其中之一时， 忽略pg_collation中的ICU排序规则项。 试图使用其中一个将会抛出一个类似“collation "de-x-icu" for encoding "WIN874" does not exist”的错误。

2.3. 创建新的排序规则对象

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

与所有预定于的对象一样，标准和预定义的排序规则在模式 pg_catalog中。用户定义的排序规则应该在用户模式中创建。 这也确保它们由pg_dump保存。

2.3.1. libc 排序规则

可以像这样创建新的libc排序规则：

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

由于预定义的libc排序规则已经包含了数据库实例初始化时在操作系统中定义的所有排序规则， 因此通常不需要手动创建新排序规则。如果需要不同的命名系统（在这种情况下， 另请参阅第 23.2.2.3.3 节）， 或者操作系统已经升级以提供新的区域设置定义（在这种情况下， 另请参阅pg_import_system_collations()）， 可能需要手动创建。

2.3.2. ICU 排序规则

ICU允许自定义超出由initdb 预加载的基本语言+国家/地区集的排序规则。鼓励用户定义他们自己的排序规则对象， 利用这些条件来满足他们排序行为的需求。请参阅 http://userguide.icu-project.org/locale和http://userguide.icu-project.org/collation/api 获取有关ICU区域设置命名的信息。可接受的名称和属性集取决于特定的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环境中任意指定排序规则对象的名称。 在这个例子中，我们遵循预定义排序规则使用的命名风格， 而这种风格又遵循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，使用表情符号排序规则类型的根排序规则观察传统ICU区域命名系统中的方式，根区域设置由空字符串选择。

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

  在拉丁字母之前对希腊字母进行排序。（默认为拉丁语在希腊语之前。）

• 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-grek-latn'); CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=grek-latn');

  结合上述两个选项。

• 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获取详细信息。 可能的排序规则类型（co子标签）列表可以在 CLDR 仓库中找到。 区域设置浏览器 可以用于检查一个特定区域设置定义的细节。使用k* 子标签的示例至少要求ICU版本54。

请注意，虽然此系统允许创建“忽略大小写”或“忽略重音符”或类似（使用ks键）的排序规则，但为了使这些排序规则真正以大小写敏感或重音敏感方式工作，它们也需要在CREATE COLLATION中定义为非确定性的；见第 23.2.2.4 节。否则，根据排序规则比较相等但按照字节不相等的任何字符串将根据其字节值进行排序。

注意

根据设计，ICU几乎可以接受任何字符串作为区域名称， 并使用其文档中描述的后备程序将其与最接近的区域设置相匹配。因此， 如果使用给定ICU安装实际上不支持的功能组合排序规范，则不会有直接反馈。 因此建议创建应用程序级别的测试用例，以检查排序规则定义是否满足需求。

2.3.3. 复制排序规则

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

2.4. 非确定性排序规则

排序规则要么是确定性的，要么是非确定性的。 确定性排序规则使用确定性比较方法，也就意味着字符串要认为是相等的话，只有在它们是由相同的字节顺序组成的时候。 非确定性比较可以确定字符串相等，即使它们由不同字节组成。 典型的情形包括大小写敏感比较、重音敏感比较和不同Unicode规范形式的字符串的比较。 非敏感比较的实际是由排序规则提供程序来实现的；确定性标志仅确定使用按字节比较时关系是否要断开。 也可参见Unicode技术标准10获得更多术语信息。

创建非确定性排序规则时，指定属性deterministic = false以CREATE COLLATION，例如：

该例子以非确定性方式使用标准的Unicode排序规则。特别地，这将允许不同规范形式的字符串能正确地比较。更有趣的例子是使用上述的ICU定制化功能。例如：

所有标准和预定义的排序规则是确定性的，所有用户自定义的排序规则缺省是确定性的。 尽管非确定性规则提供了更“正确”的行为，尤其是考虑到Unicode和其许多特殊情况的全部能力时，也有一些缺点。 首先，使用它们会导致性能下降。特别请注意，B-tree不能将重复数据消除与使用非确定性排序的索引一起使用。 此外，用非确定性规则排序时某些操作就不可能做，例如模式匹配。因此，仅在特别需要它们的情况下才使用。

提示

要处理不同 Unicode 规范化格式中的文本，还有一个选项，使用函数/表达式normalize 和 is normalized预处理或检查字符串，而不是使用非确定性排序规则。每种方法都有不同的权衡。

pg_collation

系统视图pg_collation描述了可用的排序规则，其本质是从一个SQL名字到操作系统locale分类的映射。更多信息参见第 23.2 节。

表 51.12. pg_collation Columns

注意在这个目录中的唯一键是（collname、 collencoding、 collnamespace）， 不仅仅是（collname，collnamespace）。 所有collencoding不等于当前数据库编码或-1的编码规则通常都会被PostgreSQL忽略，且禁止创建和collencoding = -1的项重名的项。因此使用一个受限的SQL名字（schema.name）来标识一个排序规则是足够的，即使这根据目录定义是不唯一的。以这种方式定义这个目录的原因是initdb会在集簇初始化时使用系统上所有可用的locale填充这个目录，所以它必须能够为所有可能在集簇中使用的编码保持项。

在template0数据库中，创建与数据库编码不匹配的编码是有用的，因为它们可以匹配后面从template0克隆的数据库的编码。这在目前必须手动完成。

CREATE COLLATION

CREATE COLLATION — 定义一种新排序规则

大纲

描述

CREATE COLLATION使用指定的操作系统区域 设置或者复制一个现有的排序规则来定义新的排序规则。

要创建一种排序规则，你必须拥有目标模式上的 CREATE特权。

参数

• IF NOT EXISTS

  如果已经存在了同名的排序规则，则不要抛出错误。在这种情况下发出一个通知。 请注意，不保证已经存在的排序规则与要创建的这个类似。

• name

  排序规则的名字，可以被模式限定。如果没有用模式限定，该排序规则 会被定义在当前模式中。排序规则名称在其所处的模式中必须唯一（系统 目录可以为其他编码包含具有相同名称的排序规则，但数据库编码不匹配 时它们会被忽略）。

• locale

  这是一种一次设置LC_COLLATE 和LC_CTYPE的快捷方式。如果你指定它，你就 不能指定那两个参数。

• lc_collate

  为LC_COLLATE区域分类使用指定的操作系统 区域。

• lc_ctype

  为LC_CTYPE区域分类使用指定的操作系统 区域。

• provider

  指定用于与此排序规则相关的区域服务的提供程序。可能的值是： icu、 libc。 默认是libc。 可用的选择取决于操作系统和构建选项。

• DETERMINISTIC

  规定排序是否应使用确定性比较。默认值为true。确定性比较认为那些在字节上不相等的字符串是不相等的，即使它们在逻辑上被比较认为相等。 PostgreSQL使用字节比较来断开连接。不确定的比较可能会使排序规则大小写不敏感或音调不敏感。 因此，您需要选择一个适当的LC_COLLATE设置并且\在此处将排序规则设置为不确定。非确定性排序规则仅被ICU提供者支持。

• version

  指定使用该排序规则存储的版本字符串。通常忽略该选项， 这会导致版本从操作系统提供的排序规则实际版本中计算出来。 此选项旨在供pg_upgrade用于复制现有安装中的版本。又见ALTER COLLATION获取如何处理排序规则版本错误匹配。

• existing_collation

  要复制的一种现有的排序规则的名称。新的排序规则将和现有的具有 同样的属性，但是它是一个独立的对象。

注解

CREATE COLLATION 采用SHARE ROW EXCLUSIVE锁，在pg_collation系统分类上，这是自我冲突的，所以一次只能运行一条CREATE COLLATION 命令。

使用DROP COLLATION可移除用户定义的排序规则。

关于如何创建排序规则的更多信息可见第 23.2.2.3 节。

使用libc排序规则提供程序时，语言环境必须适用于当前的数据库编码。 有关精确的规则，请参见CREATE DATABASE。

示例

从操作系统区域fr_FR.utf8创建一种排序规则（假定 当前数据库编码是UTF8）：

使用German phone book排序顺序使用ICU提供程序创建排序规则：

从一个现有的排序规则创建一个新的排序规则：

能在应用中使用与操作系统无关的排序规则名称就很方便了。

兼容性

在 SQL 标准中有一个CREATE COLLATION 语句，但是它被限制为只能复制一个现有的排序规则。创建新排序规则的 语法是一种PostgreSQL扩展。

ALTER COLLATION

ALTER COLLATION — 更改一个排序规则的定义

大纲

描述

ALTER COLLATION更改一个排序规则的定义。

你必须拥有要对其使用ALTER COLLATION的排序规则。要更改拥有者，你必须是新的拥有角色的直接或者间接成员，并且该角色必须在排序规则的模式上具有CREATE特权（这些限制强制要求拥有者不能通过丢弃并重建该排序规则来做任何你不能做的事情。不过，一个超级用户可以更改任何排序规则的所有权）。

参数

• name

  一个现有排序规则的名称（可以是模式限定的）。

• new_name

  排序规则的新名称。

• new_owner

  排序规则的新拥有者。

• new_schema

  排序规则的新模式。

• REFRESH VERSION

  更新排序规则的版本。 参阅下面的Notes。

注意

使用ICU库提供的排序规则时，创建排序规则对象时，系统目录中会记录排序规则的特定ICU版本。 使用排序规则时，将根据记录的版本检查当前版本，并在发生不匹配时发出警告，例如：

排序规则定义的更改会导致索引损坏和其他问题，因为数据库系统依赖于具有特定排序顺序的存储对象。 通常，应该避免这种情况，但它可以在合法的情况下发生，例如使用pg_upgrade 升级到与更新版本的ICU链接的服务器二进制文件。发生这种情况时， 应该重建所有依赖于该排序规则的对象，例如，使用REINDEX。 完成后，使用命令ALTER COLLATION ... REFRESH VERSION可以刷新排序规则版本。 这将更新系统目录以记录当前的排序规则版本，并会使警告消失。请注意， 这实际上并不检查是否所有受影响的对象都已正确重建。

使用 libc 提供的排序规则，并且 PostgreSQL 是使用GNU C库构建的，则将C库的版本用作排序规则版本。由于排序规则定义通常仅随GNU C库发行版而更改， 因此可以提供一些防止损坏的防御措施，但它并不是完全可靠的。

当前，没有针对数据库默认排序规则的版本跟踪。

以下查询可用于识别当前数据库中需要刷新的所有排序规则以及依赖它们的对象：

例子

要把排序规则de_DE重命名为german：

要把排序规则en_US的拥有者改成joe：

兼容性

在 SQL 标准中没有ALTER COLLATION语句。

DROP COLLATION

DROP COLLATION — 移除一个排序规则

大纲

描述

DROP COLLATION移除一个之前 定义好的排序规则。要能删除一个排序规则，你必须拥有它。

参数

• IF EXISTS

  如果该排序规则不存在则不要抛出一个错误，而是发出一个提示。

• name

  排序规则的名称。排序规则名称可以是模式限定的。

• CASCADE

  自动删除依赖于该排序规则的对象，然后删除所有 依赖于那些对象的对象（见第 5.14 节）。

• RESTRICT

  如果有任何对象依赖于该排序规则，则拒绝删除它。这是默认值。

示例

要删除名为german的排序规则：

兼容性

除了IF EXISTS选项之外， DROP COLLATION命令符合 SQL标准。该选项是一个 PostgreSQL扩展。