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

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

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

Объект «правила сортировки» (collation) является объектом схемы SQL, сопоставляющим имя SQL с локалями, которые предоставляются установленными в операционной системе библиотеками. Определение правил сортировки содержит параметр provider , который определяет библиотеку, предоставляющую данные локали. Одним из стандартных имен провайдеров является libc, использующий локали из библиотеки C операционной системы. Эти локали используются большинством системных инструментов операционной системы. Другим провайдером является icu, который использует внешнюю библиотеку ICU library.

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

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

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

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

CREATE COLLATION german (provider = icu, locale = 'de-DE');

CREATE COLLATION german FROM "de_DE";
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);

ICU предоставляет широкие возможности управления поведением сортировки посредством определения новых правил сортировки, где параметры сортировки указываются как часть языкового тега. Эти параметры позволяют адаптировать порядок сортировки под различные задачи. Например:

-- игнорирование различий в диакритических знаках и регистре
CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true
SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true

-- заглавные буквы сортируются перед строчными
CREATE COLLATION upper_first (provider = icu, locale = 'und-u-kf-upper');
SELECT 'B' < 'b' COLLATE upper_first; -- true

-- числовая сортировка цифр и игнорирование пунктуации
CREATE COLLATION num_ignore_punct (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-kn');
SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- true
SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true

Многие из доступных параметров описаны в Раздел 3.8.2.3.2, или см. Раздел 3.8.2.3.5 для получения более подробных сведений.

CREATE COLLATION level3 (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-level3');
CREATE COLLATION level4 (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-level4');
CREATE COLLATION identic (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-identic');

-- невидимый разделитель игнорируется на всех уровнях, кроме identic
SELECT 'ab' = U&'a\2063b' COLLATE level4; -- true
SELECT 'ab' = U&'a\2063b' COLLATE identic; -- ложь

-- пунктуация игнорируется на уровне 3, но не на уровне 4
SELECT 'x-y' = 'x_y' COLLATE level3; -- истина
SELECT 'x-y' = 'x_y' COLLATE level4; -- ложь

CREATE COLLATION custom (provider = icu, locale = 'und', rules = '&V << w <<< W');

CREATE COLLATION ebcdic (provider = icu, locale = 'und',
rules = $$
& ' ' < '.' < '<' < '(' < '+' < \|
< '&' < '!' < '$' < '*' < ')' < ';'
< '-' < '/' < ',' < '%' < '_' < '>' < '?'
< '`' < ':' < '#' < '@' < \' < '=' < '"'
<*a-r < '~' <*s-z < '^' < '[' < ']'
< '{' <*A-I < '}' <*J-R < '\' <*S-Z <*0-9
$$);

SELECT c
FROM (VALUES ('a'), ('b'), ('A'), ('B'), ('1'), ('2'), ('!'), ('^')) AS x(c)
ORDER BY c COLLATE ebcdic;
 c
---
 !
 a
 b
 ^
 A
 B
 1
 2