Работа с акцентами в PostgreSQL: поддержка и решения

Быстрый ответ

Начиная с версии PostgreSQL 12, имеется возможность реализации поиска без учета акцентов с использованием расширения ICU. Для этого требуется создать специализированную коллацию:

CREATE COLLATION ci_ai (provider = icu, locale = 'en-US-x-icu', deterministic = false);

SELECT * FROM tbl WHERE col COLLATE ci_ai = 'cafe';

Данная команда создает коллацию ci_ai, которая игнорирует акценты. Это позволяет искать значения в столбце col таблицы tbl.

Оптимизация запроса: использование модуля unaccent и других инструментов

Модуль unaccent: Ваш щит против акцентов

Чтобы увеличить эффективность запросов, рекомендуется использовать модуль unaccent PostgreSQL, в особенности, когда речь идет о данных, вносимых пользователями:

CREATE EXTENSION IF NOT EXISTS unaccent;

SELECT * FROM tbl WHERE unaccent(col) = unaccent('Café');

Подробнее об этом расскажет наш спикер на видео

Неизменяемая обёртка: быстрые и безопасные запросы

Для оптимизации использования unaccent в индексах, его следует обернуть в неизменяемую пользовательскую функцию:

CREATE OR REPLACE FUNCTION ia_unaccent(text) RETURNS text AS $$
SELECT unaccent('unaccent', $1)
$$ IMMUTABLE LANGUAGE sql;

CREATE INDEX idx_col_unaccent ON tbl USING btree (ia_unaccent(col));

Продвинутая работа с шаблонами: unaccent и pg_trgm — идеальное сочетание

Для эффективной работы с шаблонами LIKE/ILIKE сочетайте unaccent с pg_trgm:

CREATE INDEX trgm_idx ON tbl USING gin (col gin_trgm_ops);

SELECT * FROM tbl WHERE unaccent(col) LIKE unaccent('%cafe%');

Используйте индексы для улучшения происходительности поиска по шаблонам с левым якорем:

SELECT * FROM tbl WHERE unaccent(col) LIKE unaccent('Café%');

Полнотекстовый поиск и его особенности

Применение полнотекстового поиска (FTS)

Настройте словари FTS на удаление акцентов для оптимизации результатов поиска:

ALTER TEXT SEARCH CONFIGURATION public.english ADD MAPPING FOR hword, hword_part, word WITH english_stem, unaccent;

В простых ситуациях может быть достаточно прямого использования unaccent.

Создание специализированных коллаций

В PostgreSQL коллации базируются на локальных настройках операционной системы. Создание коллаций, игнорирующих акценты – процесс сложный, для этого лучше воспользоваться представленными выше функциями и расширениями.

Визуализация

Представьте себе библиотеку, где звучание книг ранжировано не алфавитно, а с учетом их звучания:

📚 Обычная алфавитная сортировка:   А, Б, В, Г...
📚 Коллация, игнорирующая акценты:  А, À, Á, Ä, Б...

В PostgreSQL это будет выглядеть так:

SELECT * FROM books ORDER BY title COLLATE "und-x-icu";

Итак, имеем:

📚 "Café" будет располагаться рядом с "Cafe"
📚 "Éclair" станет соседом "Eclair"

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

Важные замечания и обходные пути

Учёт акцентов: специальные случаи

С PostgreSQL 9.6 поддерживается адекватное отображение таких лигатур, как 'æ' и 'œ':

SELECT * FROM words WHERE word COLLATE "und-x-icu" = 'œuvre';

Коллации и их ограничения

Коллации используются при сортировке, но не влияют на операции сравнения, такие как unaccent.

SELECT * FROM phrases WHERE unaccent(expression) = unaccent('Résumé');

Создание надежной системы

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

Полезные материалы

1. PostgreSQL: Documentation: 16: 24.2. Collation Support — официальная документация по коллациям PostgreSQL.
2. How to make "case-insensitive" query in Postgresql? – Stack Overflow — примеры запросов, игнорирующих акценты, от сообщества Stack Overflow.
3. FAQ – PostgreSQL wiki — часто задаваемые вопросы о использовании COLLATE в PostgreSQL.
4. Postgres OnLine Journal — руководство по полнотекстовому поиску в PostgreSQL.
5. UTS #10: Unicode Collation Algorithm — подробности о коллационном алгоритме Unicode.
6. Seite wurde nicht gefunden. – CYBERTEC — инструкция по созданию индексов в PostgreSQL, игнорирующих регистр и акценты.