23.2. Поддержка правил сортировки#
23.2. Поддержка правил сортировки
Функция правил сортировки позволяет указывать порядок сортировки и поведение классификации символов данных для каждого столбца или даже для каждой операции. Это позволяет обойти ограничение, что настройки LC_COLLATE и LC_CTYPE базы данных не могут быть изменены после ее создания.
23.2.1. Концепции
Концептуально, у каждого выражения сортируемых типов данных, есть правило сортировки. (Встроенные типы данных, которые могут быть упорядочены, это text, varchar и char. Пользовательские базовые типы также могут быть помечены как упорядочиваемые, и, конечно же, домен над упорядочиваемым типом данных также является упорядочиваемым). Если выражение является ссылкой на столбец, правило сортировки выражения - это определенное правило сортировки столбца. Если выражение является константой, правило сортировки - это правило сортировки по умолчанию для типа данных константы. Правило сортировки более сложного выражения основывается на правилах сортировки его входных данных, как описано ниже.
Правило сортировки выражения может быть правилом сортировки “по умолчанию”, что означает настройки локали, определенные для базы данных. Также возможно, что правило сортировки выражения будет неопределенным. В таких случаях операции сортировки и другие операции, которым необходимо знать правило сортировки, завершатся с ошибкой.
Когда система базы данных должна выполнить упорядочение или классификацию символов, она использует правило сортировки входного выражения. Это происходит, например, с помощью предложений 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";
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” считаются буквами, а сортировка выполняется строго по значениям байтов символов.
Дополнительно, стандартное имя правила сортировки SQL ucs_basic
доступно для кодировки UTF8. Оно эквивалентно
C и сортирует по кодовым точкам Юникода.
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 могут использоваться независимо от кодировки базы данных.
Tantor SE считает различные объекты правила сортировки несовместимыми, даже если они имеют идентичные свойства. Таким образом, например,
SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;
выдаст ошибку, даже если правила сортировки C и POSIX
имеют идентичное поведение. Поэтому не рекомендуется смешивать
усеченные и полные имена сортировок.
23.2.2.2.2. Правила сортировки ICU
С использованием ICU нет смысла перечислять все возможные имена локалей. ICU использует определенную систему именования для локалей, но существует гораздо больше способов назвать локаль, чем на самом деле существует отдельных локалей. Команда initdb использует API ICU для извлечения набора отдельных локалей для заполнения начального набора правил сортировки. Правила сортировки, предоставляемые ICU, создаются в среде SQL с именами в формате языкового тега BCP 47, с добавлением расширения “private use” -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 “корневое” правило сортировки. Используйте это для получения разумного языконезависимого порядка сортировки.
Некоторые (реже используемые) кодировки не поддерживаются 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. Пользователям рекомендуется определить собственные объекты правила сортировки, которые используют эти возможности, чтобы адаптировать поведение сортировки под свои требования.
См. https://unicode-org.github.io/icu/userguide/locale/ и https://unicode-org.github.io/icu/userguide/collation/api.html для получения информации о наименовании локали 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');Немецкое правило сортировки с типом сортировки телефонного справочника
Первый пример выбирает локаль 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');Корневое правило сортировки с правилом сортировки Emoji, согласно стандарту Unicode Technical Standard #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. Недетерминированные правила сортировки
Правило сортировки может быть либо детерминированной, либо недетерминированной. Детерминированное правило сортировки использует детерминированные сравнения, что означает, что строки считаются равными только в том случае, если они состоят из одинаковой последовательности байтов. Недетерминированное сравнение может считать строки равными даже если они состоят из разных байтов. Типичные ситуации включают сравнение без учета регистра, сравнение без учета акцентов, а также сравнение строк в разных нормальных формах Юникода. Фактическая реализация таких сравнений без учета регистра зависит от провайдера правила сортировки; флаг детерминированности определяет только то, как разрешаются равные значения с помощью сравнения по байтам. См. также Технический стандарт Юникода 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);
Все стандартные и предопределенные правила сортировки являются детерминированными, все пользовательские правила сортировки по умолчанию являются детерминированными. В то время как недетерминированные правила сортировки обеспечивают более "правильное" поведение, особенно при учете всей мощи Юникода и его множества особых случаев, они также имеют некоторые недостатки. Прежде всего, их использование приводит к снижению производительности. Обратите внимание, в частности, что B-дерево не может использовать дедупликацию с индексами, использующими недетерминированное правило сортировки. Также некоторые операции невозможны с недетерминированными правилами сортировки, такие как операции сопоставления шаблонов. Поэтому их следует использовать только в тех случаях, когда они явно требуются.
Подсказка
Для работы с текстом в различных формах нормализации Unicode также есть возможность использовать функции/выражения normalize и is normalized для предварительной обработки или проверки строк вместо использования недетерминированных правил сортировки. Каждый подход имеет свои преимущества и недостатки.