Поддержка сопоставления

примечание

Эта страница переведена при помощи нейросети GigaChat.

Функция сопоставления позволяет устанавливать индивидуальный порядок сортировки и правила классификации символов для данных в отдельных столбцах или операциях. Это решает проблему невозможности изменения параметров LC_COLLATE и LC_CTYPE после создания базы данных.

Концепции

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

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

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

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

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

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

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

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

Например, рассмотрим это определение таблицы:

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

Тогда в

SELECT a < 'foo' FROM test1;

сравнение выполняется в соответствии с < правилами, потому что выражение сочетает неявно полученную сортировку с сортировкой по умолчанию. Но в

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

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

Колляция — это объект схемы SQL, который связывает имя SQL с локалями, предоставляемыми библиотеками операционной системы. Колляция имеет параметр поставщика, указывающий, какая библиотека предоставляет данные о локали. Один из стандартных поставщиков — libc, который использует локали, предоставляемые системной библиотекой C. Эти локали применяются большинством инструментов операционной системы. Другим поставщиком является icu, использующая внешнюю библиотеку ICU. Локали ICU возможны только при предварительной настройке поддержки ICU при сборке PostgreSQL.

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

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

Стандартные сопоставления

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

Также для кодировки UTF8 доступно стандартное сопоставление SQL под названием ucs_basic, эквивалентное поведению C и сортирующее символы по их кодовым позициям Unicode.

Предопределенные сопоставления

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

Узнать актуальные доступные локали можно с помощью запроса SELECT * FROM pg_collation или команды \dOS+ в интерактивной оболочке psql.

Сортировка 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, или если в операционной системе появились новые локали после инициализации, можно создать новое сопоставление с помощью команды СОЗДАТЬ СРАВНЕНИЕ. Новые системные локали можно массово импортировать с помощью функции pg_import_system_collations().

В конкретной базе данных интерес представляют только те сопоставления, которые используют ее кодировку. Остальное содержимое каталога pg_collation игнорируется. Значит, укороченное имя сопоставления, например de_DE, может считаться уникальным в рамках базы данных, даже если оно не уникально в общем смысле. Рекомендуется использовать короткие имена сопоставлений, так как это облегчит миграцию в случае смены кодировки базы данных. Однако сопоставления default, C и POSIX работают независимо от кодировки базы данных.

PostgreSQL трактует разные объекты сопоставления как несовместимые, даже если их поведение идентично. Например, даже если сопоставления C и POSIX работают одинаково, они все равно воспринимаются как разные.

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

Подобный запрос вызовет ошибку, несмотря на сходство поведения колляций C и POSIX. Поэтому смешивание сокращенных и полных наименований сопоставлений не рекомендуется.

Сортировка ICU

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

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

Создание новых объектов сопоставления

Если стандартных и предопределенных сопоставлений недостаточно, пользователи могут создавать собственные сопоставления с помощью команды SQL СОЗДАТЬ СРАВНЕНИЕ.

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

Колляции libc

Новые сопоставления символов для библиотеки C можно создать следующим образом:

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

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

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

Колляции 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. В противном случае любые строки, которые сравниваются одинаково согласно сопоставлению, но не равны побайтово, будут отсортированы в соответствии со своими байтовыми значениями.

Примечание

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

Копирование сопоставлений

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

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

Недеффициентные сопоставления

Сопоставление может быть либо детерминированным, либо недетерминированным. Детерминированное сопоставление использует точные байтовые сравнения, считая строки равными только при полном совпадении последовательности байтов. Недетерминированное сопоставление допускает равенство строк, состоящих из разных байтов. Примером таких ситуаций являются регистронезависимые сравнения, акцентонечувствительные сравнения и сравнения строк в различных нормальных формах Unicode. Конкретная реализация нечувствительного сравнения зависит от поставщика сопоставления, а флаг детерминированности лишь указывает, должны ли совпадения проверяться побайтно. Дополнительную информацию о терминологии можно найти в Unicode Technical Standard №10.

Чтобы создать недетерминированное сопоставление, добавьте свойство deterministic = false в команду CREATE COLLATION, например:

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

Этот пример будет использовать стандартную сортировку Юникода недетерминированным образом. В частности, это позволит правильно сравнивать строки в разных нормальных формах. Более интересные примеры используют возможности настройки 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-дерево не может использовать дедупликацию с индексами, которые используют недетерминированную сортировку. Кроме того, определенные операции невозможны при использовании недетерминированных сортировок, такие как операции сопоставления с образцом. Поэтому они должны использоваться только в тех случаях, когда они специально нужны.

Совет

Для работы с текстом в разных формах нормализации Юникода также можно использовать функции/выражения normalize и is normalized для предварительной обработки или проверки строк вместо использования недетерминированных сопоставлений. Для каждого подхода есть разные компромиссы.