Пять лет тому назад я уже писал о сортировке результата SQL-запроса по строковому полю содержащему комбинацию букв и цифр в "естественном" порядке. Естественный порядок сортировки – это способ упорядочивания строк, при котором вложенные числа рассматриваются как целые числовые значения, а не как последовательность символов. Тогда мной рассматривалось создание функций, которые реализуют такую сортировку под MS SQL Server, Oracle и PostgreSQL. В новом проекте на PostgreSQL мы снова столкнулись с необходимостью сортировки строк в естественном порядке. Когда я предложил разработчикам свою функцию, коллега мне подсказал другое решение – для полей нуждающихся в естественном порядке сортировки создать свой специальный COLLATION.
COLLATION – это набор правил определяющих, как символы сопоставляются друг с другом во время сравнения и сортировки текстовых данных. В стандартной установке PostgreSQL они не имеют поддержки естественной сортировки. Но PostgreSQL позволять создать собственные COLLATION:
CREATE COLLATION [ IF NOT EXISTS ] имя ( [ LOCALE = локаль, ] [ LC_COLLATE = категория_сортировки, ] [ LC_CTYPE = категория_типов_символов, ] [ PROVIDER = провайдер, ] [ DETERMINISTIC = boolean, ] [ RULES = правила, ] [ VERSION = версия ] );Имя COLLATION может быть дополненное схемой. Если схема не указана, то он создается в текущей схеме пользователя. Чтобы вызвать команду пользователю необходимо в целевой схеме иметь право CREATE.
У команды CREATE COLLATION нас интересуют два параметра:
- PROVIDER – библиотека, предоставляющая правила сортировки. Стандартная библиотека libc не поддерживает естественный порядок сортировки. Поэтому мы должны воспользоваться International Components for Unicode (ICU). ICU – это open-source библиотеки для C/C++ (ICU4C) и Java (ICU4J), которые позволяют программам работать с разными языками, алфавитами, датами и часовыми поясами. Эти библиотеки даже встроены в операционные системы (Windows, Linux, Android, macOS), и используются в подуктах многих известных компаний (Google, Microsoft, Apple, IBM, Oracle, Adobe, Amazon, Intel...) и open-source проектов (Apache, LibreOffice, Mozilla, Wikipedia...). Если при выполнении CREATE COLLATION c PROVIDER = icu возникнет ошибка "ICU is not supported", то значит ваша сборка PostgreSQL была скомпилирована без флага --with-icu.
- LOCALE – имя локали, которое состоит из языковой метки и набора правил для работы с текстом. Языковые метки определяются стандартом IETF BCP 47, используемым для обозначения человеческих языков и регионов в программном обеспечении. Например: "en" обозначает английский язык, а "en-US" – это вариант английского языка для США (язык дополнен кодом региона).
На что конкретно влияет языковая метка в LOCALE?- На уникальные буквы и диакритические знаки (умлауты, тильды). В разных языках одни и те же символы сортируются совершенно по-разному. Универсальный язык und (undetermined) использует стандартный порядок Unicode, но в национальных алфавитах правила могут отличаться. Например:
- Буква Å. В норвежском языке (no) она считается полноценной и стоит в самом конце алфавита (после Z). В локали und или en она будет сортироваться рядом с обычной буквой A.
- Буква Ö. В немецком языке (de) она обычно сортируется как O, а в шведском (sv) – это отдельная буква и стоит в конце алфавита.
- На порядок следования алфавитов в многоязычном тексте. Если в одном поле перемешан текст на разных языках, языковая метка определяет, какой алфавит будет идти первым:
- Если выбрать ru, то сначала сортируются все символы на кириллице, а затем на латинице.
- Если выбрать en или und, то сначала пойдет латиница, а кириллица уйдет в конец списка.
- На игнорирование или учет специфических символов. В некоторых языках определенные комбинации символов имеют особый приоритет при сравнении, который зашит в используемые ICU языковые стандарты CLDR (Common Locale Data Repository).
- На уникальные буквы и диакритические знаки (умлауты, тильды). В разных языках одни и те же символы сортируются совершенно по-разному. Универсальный язык und (undetermined) использует стандартный порядок Unicode, но в национальных алфавитах правила могут отличаться. Например:
Создадим COLLATION с сортировкой в "естественном" порядке для универсального, норвежского, английского и русского языков:
CREATE COLLATION IF NOT EXISTS natural_sort_und (PROVIDER = icu, LOCALE = 'und-u-kn-true'); CREATE COLLATION IF NOT EXISTS natural_sort_no (PROVIDER = icu, LOCALE = 'no-u-kn-true'); CREATE COLLATION IF NOT EXISTS natural_sort_en (PROVIDER = icu, LOCALE = 'en-u-kn-true'); CREATE COLLATION IF NOT EXISTS natural_sort_ru (PROVIDER = icu, LOCALE = 'ru-u-kn-true');Выполним тестовые запросы
SELECT unnest('{10, 20, 100, 10A, 40, 40Б, 40Z, 40Å}'::text[]) AS code;
SELECT unnest('{10, 20, 100, 10A, 40, 40Б, 40Z, 40Å}'::text[]) COLLATE natural_sort_und AS code ORDER BY code;
SELECT unnest('{10, 20, 100, 10A, 40, 40Б, 40Z, 40Å}'::text[]) COLLATE natural_sort_no AS code ORDER BY code;
SELECT unnest('{10, 20, 100, 10A, 40, 40Б, 40Z, 40Å}'::text[]) COLLATE natural_sort_en AS code ORDER BY code;
SELECT unnest('{10, 20, 100, 10A, 40, 40Б, 40Z, 40Å}'::text[]) COLLATE natural_sort_ru AS code ORDER BY code;
и посмотрим, как строки сортируются в зависимости от COLLATION:
| стандартный | natural_sort_und | natural_sort_no | natural_sort_en | natural_sort_ru |
| 10 | 10 | 10 | 10 | 10 |
| 20 | 10A | 10A | 10A | 10A |
| 100 | 20 | 20 | 20 | 20 |
| 10A | 40 | 40 | 40 | 40 |
| 40 | 40Å | 40Z | 40Å | 40Б |
| 40Б | 40Z | 40Å | 40Z | 40Å |
| 40Z | 40Б | 40Б | 40Б | 40Z |
| 40Å | 100 | 100 | 100 | 100 |
Что бы в каждом SQL-запросе не указывать COLLATE для поля или в ORDER BY можно указать его для поля при создании таблицы:
CREATE TABLE x ( id SERIAL PRIMARY KEY, code VARCHAR(20) COLLATE natural_sort_und, name TEXT );или для уже существующего поля:
ALTER TABLE y ALTER COLUMN code TYPE VARCHAR(20) COLLATE natural_sort_und;Учтите, что PostgreSQL не позволит изменить COLLATE поля, если на него завязаны индексы, внешние ключи (foreign keys) или генерируемые поля (GENERATED ALWAYS AS). Сначала их придется удалить.
Итого. Что мы видим? COLLATION в PostgreSQL – это простой в настройке и использовании мощный инструмент в руках разработчика.
Комментариев нет:
Отправить комментарий