Поиск без учета регистра и диакритических знаков PostgreSQL
У меня есть таблица данных, и я хотел бы отфильтровать столбцы. Например, для поиска пользователя по его ФИО.
Однако я хотел бы позволить пользователю вводить поисковые фразы независимо от регистра и акцентов.
Итак, я проверил эти (и другие) источники и вопросы:
- https://stackoverflow.com/a/11007216
- Как игнорировать строки с учетом регистра в PostgreSQL
- https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC
Я думал, что недетерминированные сопоставления, наконец, могут быть правильным способом достижения этого, но, к сожалению, я не знаю, как это сделать:
- объединить case_insensitive и ignore_accents в одну сортировку
- как разрешить поиск только по подстроке в таком WHERE (например, найти «Jóhn Doe» только по строке «joh»), поскольку недетерминированные сопоставления не поддерживают LIKE или регулярные выражения
- какой индекс использовать
Буду очень признателен за любой совет, как наконец справиться с такой проблемой.
Спасибо!
Ответы 1
Создание нечувствительных к регистру и диакритическим знакам сопоставлений ICU довольно просто:
CREATE COLLATION english_ci_ai (
PROVIDER = icu,
DETERMINISTIC = FALSE,
LOCALE = "en-US-u-ks-level1"
);
Или, что то же самое (этот синтаксис также работает со старыми версиями ICU:
CREATE COLLATION english_ci_ai (
PROVIDER = icu,
DETERMINISTIC = FALSE,
LOCALE = "en-US@colStrength=primary"
);
См. подробности в документация по отделению интенсивной терапии и подробное обсуждение в моя статья.
Но ваша проблема в том, что вы хотите искать подстроку. Таким образом, вы должны создать индекс триграммы:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE INDEX ON tab USING gin (unaccent(doc) gin_trgm_ops);
Тогда вы можете искать так:
SELECT * FROM tab
WHERE unaccent(doc) ILIKE unaccent('%joh%');
Обратите внимание, что вы должны принудительно установить минимальную длину 4 или около того в строке поиска, если хотите, чтобы это было эффективно.
Спасибо за обновление, я думал, что мне придется придерживаться этого решения без акцента. Мне просто нужно создать IMMUTABLE версию unaccent, как указано в моей первой ссылке. Благодарю вас!
Спасибо, ваша статья помогла мне понять, как работает создание пользовательской сортировки ICU. Однако, к сожалению, моя проблема с невозможностью поиска с помощью недетерминированного ICU по части строки сохраняется.