23.2. Поддержка правил сортировки

Правила сортировки позволяют устанавливать порядок сортировки и особенности классификации символов в отдельных столбцах или даже при выполнении отдельных операций. Это смягчает последствия того, что параметры базы данных LC_COLLATE и LC_CTYPE невозможно изменить после её создания.

23.2.1. Основные понятия

Концептуально, каждое выражение с типом данных, к которому применяется сортировка, имеет правила сортировки. (Встроенными сортируемыми типами данных являются text, varchar, и char. Типы, определяемые в базе пользователем, могут также быть отмечены как сортируемые, и, конечно, домен на основе сортируемого типа данных является сортируемым.) Если выражение содержит ссылку на столбец, правила сортировки выражения определяются правилами сортировки столбца. Если выражение — константа, правилами сортировки являются стандартные правила для типа данных константы. Правила сортировки более сложных выражений являются производной от правил сортировки входящих в него частей, как описано ниже.

Правилами сортировки выражения могут быть правила сортировки «по умолчанию», что означает использование параметров локали, установленных для базы данных. Также возможно, что правила сортировки выражения могут не определиться. В таких случаях операции упорядочивания и другие операции, для которых необходимы правила сортировки, завершатся с ошибкой.

Когда база данных должна выполнить упорядочивание или классификацию символов, она использует правила сортировки выполняемого выражения. Это происходит, к примеру, с предложениями ORDER BY и такими вызовами функций или операторов как <. Правила сортировки, которые применяются в предложении ORDER BY, это просто правила ключа сортировки. Правила сортировки, применяемые к вызову функции или оператора, определяются их параметрами, как описано ниже. В дополнение к операциям сравнения, правила сортировки учитываются функциями, преобразующими регистр символов, такими как lower, upper, и initcap; операторами поиска по шаблону; и функцией to_char и связанными с ней.

При вызове функции или оператора правило сортировки определяется в зависимости от того, какие правила заданы для аргументов во время выполнения данной операции. Если результатом вызова функции или оператора является сортируемый тип данных, правила сортировки также используются во время разбора как определяемые правила сортировки функции или выражения оператора, когда для внешнего выражения требуется знание правил сортировки.

Определение правил сортировки выражения может быть неявным или явным. Это отличие влияет на то, как комбинируются правила сортировки, когда несколько разных правил появляются в выражении. Явное определение правил сортировки возникает, когда используется предложение COLLATE; все прочие варианты являются неявными. Когда необходимо объединить несколько правил сортировки, например, в вызове функции, используются следующие правила:

  1. Если для одного из выражений-аргументов правило сортировки определено явно, то и для других аргументов явно задаваемое правило должно быть тем же, иначе возникнет ошибка. В случае присутствия явного определения правила сортировки, оно становится результирующим для всей операции.

  2. В противном случае все входные выражения должны иметь одни и те же неявно определяемые правила сортировки или правила сортировки по умолчанию. Если присутствуют какие- либо правила сортировки, отличные от заданных по умолчанию, получаем результат комбинации правил сортировки. Иначе результатом станут правила сортировки, заданные по умолчанию.

  3. Если среди входных выражений есть конфликтующие неявные правила сортировки, отличные от заданных по умолчанию, тогда комбинация рассматривается как имеющая неопределённые правила сортировки. Это не является условием возникновения ошибки, если вызываемой конкретной функции не требуются данные о правилах сортировки, которые ей следует применить. Если же такие данные требуются, это приведёт к ошибке во время выполнения.

В качестве примера рассмотрим данное определение таблицы:

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

23.2.2. Управление правилами сортировки

Правила сортировки представляют собой объект схемы SQL, который сопоставляет SQL-имя с локалью, реализуемой библиотекой, установленной в операционной системе. В определении правила сортировки задаётся провайдер, то есть библиотека, реализующая правило сортировки. Стандартный провайдер с именем libc использует системную библиотеку C и предоставляет её локали. Именно эти локали используются большинством утилит операционной системы. Также есть провайдер icu, который использует внешнюю библиотеку ICU. Локали ICU можно использовать, только если поддержка ICU была включена в конфигурации сборки PostgreSQL.

Правило сортировки, предоставляемое провайдером libc, сопоставляется с комбинацией параметров LC_COLLATE и LC_CTYPE, которую может принять системный вызов setlocale(). (Основная цель правила сортировки — настроить параметр LC_COLLATE, который управляет упорядочиванием символов. Однако на практике редко требуется иметь значение LC_CTYPE, отличное от LC_COLLATE, поэтому удобнее объединить их в одну сущность, и не создавать отдельную инфраструктуру для указания LC_CTYPE в выражениях.) Правила сортировки libc также связаны с кодировкой набора символов (см. Раздел 23.3). Одно и то же имя правила сортировки может существовать для разных кодировок.

Объект правила сортировки, предоставляемой провайдером icu, сопоставляется с именованным сортировщиком, реализуемым библиотекой ICU. ICU не поддерживает различные характеристики «collate» и «ctype», так что они всегда совпадают. Кроме того, правила сортировки ICU не зависят от кодировки, так что в базе данных будет всего одно правило сортировки ICU с определённым именем.

23.2.2.1. Стандартные правила сортировки

На всех платформах доступны правила сортировки под названием default, C, и POSIX. Дополнительные правила сортировки могут быть доступны в зависимости от поддержки операционной системы. Правило сортировки default использует значения LC_COLLATE и LC_CTYPE, заданные при создании базы данных. Правила сортировки C и POSIX определяют поведение, характерное для «традиционного C», в котором только знаки кодировки ASCII от «A» до «Z» рассматриваются как буквы, и сортировка осуществляется строго по символьному коду байтов.

Для кодировки UTF8 дополнительно поддерживается имя ucs_basic, определённое в стандарте SQL. Это правило сортировки равнозначно правилу C и производит сортировку по кодам символов Unicode.

23.2.2.2. Предопределённые правила сортировки

Если операционная система поддерживает использование нескольких локалей в одной программе (newlocale и связанные функции) или включена поддержка ICU, то при инициализации кластера баз данных программа initdb наполняет системный каталог pg_collation информацией обо всех локалях, которые обнаруживает в этот момент в операционной системе.

Для просмотра всех имеющихся локалей выполните запрос SELECT * FROM pg_collation или команду \dOS+ в psql.

23.2.2.2.1. Правила сортировки libc

Например, операционная система может предоставлять локаль с именем de_DE.utf8. При этом программа initdb создаст правило сортировки с именем de_DE.utf8 для кодировки 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 идентично. По этой причине смешивать усечённые и полные имена правил сортировки не рекомендуется.

23.2.2.2.2. Правила сортировки ICU

С ICU не представляется разумным перечислять все возможные имена локалей. ICU использует для локалей определённую схему именования, но имён локалей может быть гораздо больше, чем собственно различных локалей. Программа initdb, используя API ICU, извлекает список различных локалей и наполняет начальный набор правил в базе данных. Правила сортировки провайдера ICU создаются с именами, включающими метку языка в формате 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 («undefined», неопределённая)

«Корневое» правило сортировки ICU. Оно устанавливает разумный языконезависимый порядок сортировки.

Некоторые (редко используемые) кодировки не поддерживаются ICU. Когда база имеет одну из таких кодировок, записи правил сортировки ICU в pg_collation игнорируются. При попытке использовать их будет выдана ошибка с сообщением вида «правило сортировки "de-x-icu" для кодировки "WIN874" не существует».

23.2.2.3. Создание новых правил сортировки

Если стандартных и предопределённых правил сортировки недостаточно, пользователи могут создавать собственные правила сортировки, используя команду SQL CREATE COLLATION.

Стандартные и предопределённые правила сортировки находятся в схеме pg_catalog, как и все предопределённые объекты. Пользовательские правила сортировки должны создаваться в пользовательских схемах. Помимо прочего, это полезно тем, что их будет выгружать pg_dump.

23.2.2.3.1. Правила сортировки libc

Новые правила сортировки libc могут создаваться так:

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

Точные значения, которые могут допускаться в предложении locale в этой команде, зависят от операционной системы. В Unix-подобных системах их список выдаёт команда locale -a.

Так как предопределённый набор правил сортировки libc уже включает все правила сортировки, определённые в операционной системе в момент инициализации базы данных, необходимость создавать новые правила вручную обычно не возникает. Такая потребность может возникнуть, когда нужно сменить систему именования (в этом случае см. Подраздел 23.2.2.3.3) либо когда операционная система была обновлена и в ней появились новые определения локалей (в этом случае см. pg_import_system_collations()).

23.2.2.3.2. Правила сортировки ICU

ICU допускает видоизменения правил сортировки, не ограничивая пользователей наборами язык+страна, которые подготавливает initdb. Пользователи могут свободно создавать собственные объекты-правила сортировки, использующие предоставляемые средства для получения требуемых порядков сортировки. Информацию об именовании локалей ICU можно найти на страницах https://unicode-org.github.io/icu/userguide/locale/ и https://unicode-org.github.io/icu/userguide/collation/api.html. Набор допустимых имён и атрибутов зависит от конкретной версии 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');

Немецкое правило сортировки с порядком, принятым для телефонной книги

В первом примере локаль ICU выбирается по «метке языка» в формате BCP 47. Во втором примере используется традиционный принятый в 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.

Заметьте, что хотя данная система позволяет создавать правила сортировки, которые «игнорируют регистр» или «игнорируют ударения» и тому подобное (используя ключ ks), чтобы такие правила действительно корректно игнорировали эти аспекты, они должны объявляться в CREATE COLLATION как не детерминированные; см. Подраздел 23.2.2.4. В противном случае строки, которые считаются равными согласно правилу сортировки, но не равны побайтово, будут сортироваться по своим байтовым значениям.

Примечание

ICU по природе своей принимает в качестве имени локали практическую любую строку и сопоставляет её с наиболее подходящей локалью, которую она может предоставить, следуя процедуре выбора, описанной в её документации. Таким образом, если указание правила сортировки будет составлено с использованием характеристик, которые данная инсталляция ICU на самом деле не поддерживает, непосредственно узнать об этом нельзя. Поэтому, чтобы убедиться, что определения правил сортировки соответствует требованиям, рекомендуется проверять поведение правил на уровне приложения.

23.2.2.3.3. Копирование правил сортировки

Команда CREATE COLLATION может также создать новое правило сортировки из существующего, что может быть полезно для использования имён, независимых от операционных систем, создания имён для совместимости или использования правил сортировки ICU под более понятными именами. Например:

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

23.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, позволяющие предварительно обработать или проверить строки, и не прибегать к использованию недетерминированных правил сортировки. Однако каждый подход имеет свои минусы.