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

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

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

Объект правила сортировки, предоставляемый icu, соответствует именованному провайдеру, предоставляемому библиотекой 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 позволяет осуществлять расширенный контроль над поведением сортировки, определяя новые правила сортировки с настройками сортировки как часть языкового тега. Эти настройки могут изменять порядок сортировки в соответствии с различными потребностями. Например:

-- ignore differences in accents and case
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

-- upper case letters sort before lower case.
CREATE COLLATION upper_first (provider = icu, locale = 'und-u-kf-upper');
SELECT 'B' < 'b' COLLATE upper_first; -- true

-- treat digits numerically and ignore punctuation
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

Многие из доступных опций описаны в Раздел 22.2.3.2, или см. Раздел 22.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');

-- invisible separator ignored at all levels except identic
SELECT 'ab' = U&'a\2063b' COLLATE level4; -- true
SELECT 'ab' = U&'a\2063b' COLLATE identic; -- false

-- punctuation ignored at level3 but not at level 4
SELECT 'x-y' = 'x_y' COLLATE level3; -- true
SELECT 'x-y' = 'x_y' COLLATE level4; -- false

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