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

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

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

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

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

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

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

Затем в

SELECT a < 'foo' FROM test1;

выполняется оператор сравнения < согласно правилам ru_RU, так как выражение объединяет неявно определяемые правила сортировки с правилами, заданными по умолчанию. Но в

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

сравнение выполняется с помощью правил fr_FR, так как явное определение правил сортировки переопределяет неявное. Кроме того, в запросе

SELECT a < b FROM test1;

анализатор запросов не может определить, какое правило сортировки использовать, поскольку столбцы a и b имеют конфликтующие неявные правила сортировки. Так как оператору < требуется знать, какое правило использовать, это приведёт к ошибке. Ошибку можно устранить, применив явное указание правил сортировки к любому из двух входных выражений. Например:

SELECT a < b COLLATE "ru_RU" FROM test1;

либо эквивалентное ему

SELECT a COLLATE "ru_RU" < b FROM test1;

С другой стороны, следующее выражение схожей структуры

SELECT a || b FROM test1;

не приводит к ошибке, поскольку для оператора || правила сортировки не имеют значения, так как результат не зависит от сортировки.

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

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

упорядочение будет происходить согласно правилам ru_RU. Но данный запрос:

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 была включена в конфигурации сборки Postgres Pro.

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

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

Вы можете задать провайдер основного правила сортировки в аргументах --locale и --lc-collate команд initdb или createdb следующим образом:

--locale=локаль[@провайдер]
--lc-collate=локаль[@провайдер]

Здесь параметр провайдер может принимать значение icu или libc, а локаль задаётся в формате libc. После символа @ может быть задан только один провайдер. Значение --lc-collate переопределяет значение --locale независимо от того, задаётся ли в этих значениях провайдер.

Если вы не укажете провайдер, для локалей C и POSIX будет выбран провайдер libc, а для других провайдер выбирается так:

Перед запуском сервера Postgres Pro проверяет, был ли кластер инициализирован сервером с той же версией ICU, даже когда провайдер основного правила сортировки — не icu. Если версии библиотек ICU различаются, сервер Postgres Pro выдаёт соответствующее сообщение.

Перед подключением к базе данных, в которой основное правило сортировки использует icu, Postgres Pro сравнивает версию этого правила сортировки с версией, предоставляемой библиотекой ICU. Если версия меняется, и вы полагаете, что это изменение может повлиять на порядок сортировки ваших данных, перестройте объекты, использующие правило сортировки по умолчанию. Postgres Pro выдаёт предупреждения в случае несовпадения версий или если сравнить их невозможно по причине отсутствия в кластере информации о версии основного правила сортировки. Чтобы убрать эти предупреждения, вы можете выполнить команду ALTER COLLATION "default" REFRESH VERSION, как описано в ALTER COLLATION.

Понять, какое правило сортировки является основным в базе данных и какой провайдер оно использует, можно по значению pg_database.datcollate:

локаль@провайдер

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

CREATE COLLATION russian (provider = libc, locale = 'ru_RU');

CREATE COLLATION russian FROM "ru_RU";
CREATE COLLATION french FROM "fr-x-icu";

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

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