The post has been translated automatically. Original language: Russian
Any developer in Kazakhstan sooner or later faces this pain. You do a SELECT * FROM users ORDER BY name, and suddenly "Aliya" appears at the very end of the list, after "Yana", although it should be immediately after "Alina".
Why is this happening? Because by default, the database sorts strings by their bytecodes (C/POSIX locale), and not by linguistic rules. The letter A in Unicode has a different code than A, and the database simply does not know that they are "relatives".
Let's figure out how to set up the correct Collation for the Kazakh language in PostgreSQL.
Starting from version 10, PostgreSQL perfectly supports the ICU (International Components for Unicode) library. This is the best way, as it does not depend on the server operating system locales.
ICU support is enabled in most modern Postgres installations. You can check if the database sees the Kazakh locale.
SELECT * FROM pg_collation WHERE collname LIKE '%kk%';If the list is empty or you want to create your own explicit setting, go ahead.
We will create a special kazakh collation object that we will use for sorting.
CREATE COLLATION kazakh (PROVIDER = icu, LOCALE = 'kk-KZ');Note: If your version of Postgres is old or built without ICU, you will have to rely on system locales (libc), which is less portable (for example, on a Linux server the locale may be called kk_KZ.utf8, but on Windows it is different).
Now that we have a collage, we can use it directly in the ORDER BY:
-- Incorrect sorting (by default)
SELECT name FROM employees ORDER BY name;
-- Correct Kazakh sorting
SELECT name FROM employees ORDER BY name COLLATE kazakh;now A has taken its rightful place after A, and G after G (if there had been one).
If you do not want to write COLLATE kazakh in every query, you can set a sorting rule when creating the table.
CREATE TABLE cities (
id serial PRIMARY KEY,
name text COLLATE kazakh
);Now any ORDER BY name will automatically use the rules of the Kazakh language, and the indexes (B-tree) will be built correctly.
It is often necessary to search or sort case-insensitive data (Astana = Astana), but at the same time observe the order of the Kazakh letters.
Non-deterministic collisions have appeared in PostgreSQL 12+. We can create a rule that ignores the case.:
CREATE COLLATION kazakh_ci (
PROVIDER = icu,
LOCALE = 'kk-KZ-u-ks-level2', -- level2 ignores case, but takes accents into account
DETERMINISTIC = false
);Now you can create a table with this collation:
CREATE TABLE products (
name text COLLATE kazakh_ci
);
INSERT INTO products (name) VALUES ('Tut'), ('su'), ('Ayran');
-- Will find both 'Set', and 'set', and 'SET'
SELECT * FROM products WHERE name = 'set';Important: Using DETERMINISTIC = false imposes restrictions (for example, you cannot use such columns in LIKE without additional casts), but it is a powerful tool for accurate search and sorting.
- Do not rely on the default sorting (C/POSIX) if you are working with Cyrillic and specific characters.
- Use ICU collations (PROVIDER = icu) to be independent of the OS.
- Create a collation: CREATE kazakh COLLATION (PROVIDER = icu, LOCALE = 'kk');.
- Apply it either in a table column or in a specific query.
Now your database will respect "A", "I" and "A" just like any other letters!
Любой разработчик в Казахстане рано или поздно сталкивается с этой болью. Вы делаете SELECT * FROM users ORDER BY name, и внезапно «Әлия» оказывается в самом конце списка, после «Яна», хотя должна быть сразу после «Алины».
Почему так происходит? Потому что по умолчанию база данных сортирует строки по их байт-кодам (C/POSIX locale), а не по лингвистическим правилам. Буква Ә в кодировке Unicode имеет другой код, чем А, и база просто не знает, что они "родственники".
Давайте разберемся, как настроить правильную сортировку (Collation) для казахского языка в PostgreSQL.
Начиная с 10-й версии, PostgreSQL отлично поддерживает библиотеку ICU (International Components for Unicode). Это лучший способ, так как он не зависит от локалей операционной системы сервера.
В большинстве современных установок Postgres поддержка ICU включена. Вы можете проверить, видит ли база казахскую локаль
SELECT * FROM pg_collation WHERE collname LIKE '%kk%';Если список пуст или вы хотите создать свою явную настройку, идем дальше.
Мы создадим специальный объект коллации kazakh, который будем использовать для сортировки.
CREATE COLLATION kazakh (PROVIDER = icu, LOCALE = 'kk-KZ');Примечание: Если ваша версия Postgres старая или собрана без ICU, вам придется полагаться на системные локали (libc), что менее переносимо (например, на сервере Linux локаль может называться kk_KZ.utf8, а на Windows иначе).
Теперь, когда у нас есть коллация, мы можем использовать её прямо в ORDER BY:
-- Неправильная сортировка (по умолчанию)
SELECT name FROM employees ORDER BY name;
-- Правильная казахская сортировка
SELECT name FROM employees ORDER BY name COLLATE kazakh;теперь Ә встала на свое законное место после А, а Ғ после Г (если бы она была).
Если вы не хотите писать COLLATE kazakh в каждом запросе, можно задать правило сортировки при создании таблицы.
CREATE TABLE cities (
id serial PRIMARY KEY,
name text COLLATE kazakh
);Теперь любой ORDER BY name будет автоматически использовать правила казахского языка, а индексы (B-tree) будут построены правильно.
Часто нужно искать или сортировать данные без учета регистра (Астана = астана), но при этом соблюдать порядок казахских букв.
В PostgreSQL 12+ появились недетерминированные коллации. Мы можем создать правило, которое игнорирует регистр:
CREATE COLLATION kazakh_ci (
PROVIDER = icu,
LOCALE = 'kk-KZ-u-ks-level2', -- level2 игнорирует регистр, но учитывает акценты
DETERMINISTIC = false
);Теперь можно создать таблицу с такой коллацией:
CREATE TABLE products (
name text COLLATE kazakh_ci
);
INSERT INTO products (name) VALUES ('Сүт'), ('су'), ('Айран');
-- Найдет и 'Сүт', и 'сүт', и 'СҮТ'
SELECT * FROM products WHERE name = 'сүт';Важно: Использование DETERMINISTIC = false накладывает ограничения (например, нельзя использовать такие колонки в LIKE без дополнительных кастов), но для точного поиска и сортировки это мощнейший инструмент.
- Не полагайтесь на сортировку по умолчанию (C/POSIX), если работаете с кириллицей и специфическими символами.
- Используйте ICU collations (PROVIDER = icu), чтобы не зависеть от ОС.
- Создайте коллацию: CREATE COLLATION kazakh (PROVIDER = icu, LOCALE = 'kk');.
- Применяйте её либо в колонке таблицы, либо в конкретном запросе.
Теперь ваша база данных будет уважать «Ә», «І» и «Ң» так же, как и любые другие буквы!
Share
