PostgreSQL акцент + регистронезависимый поиск

Question

Я ищу способ поддержать с хорошими характеристиками поиск без учета регистра + без учета акцента. До сих пор у нас не было проблем с этим, используя сервер MSSql, в Oracle мы должны были использовать OracleTextи теперь нам это нужно на PostgreSQL.

Я нашел этот пост о нечувствительности к акценту:
Поддерживает ли PostgreSQL сортировку без учета акцента?

Но нам нужно сочетать это с нечувствительностью к регистру. Нам также нужно использовать индексы, иначе это может повлиять на производительность. Есть ли реальный опыт о наилучшем подходе для больших баз данных?

Answer 1

Если вам нужно "объединить с учетом регистра", есть несколько вариантов, в зависимости от ваших конкретных требований.

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

Опираясь на функцию f_unaccent() выложил в ссылочном ответе:

• Поддерживает ли PostgreSQL сортировку без учета акцента?

CREATE INDEX users_lower_unaccent_name_idx ON users(lower(f_unaccent(name)));

Затем:

SELECT *
FROM   users
WHERE  lower(f_unaccent(name)) = lower(f_unaccent('João'));

Или вы могли бы построить lower() в функцию f_unaccent(), чтобы получить что-то вроде f_lower_unaccent(),

Или (особенно если вам нужно в любом случае выполнить нечеткое сопоставление с образцом), вы можете использовать индекс триграмм, предоставленный дополнительным модулем pg_trgm, основанным на вышеуказанной функции, которая также поддерживает ILIKE, Подробности:

• LOWER LIKE vs iLIKE

Я добавил примечание к указанному ответу.

Или вы можете использовать дополнительный модуль citext:

• Отложенное, без учета регистра уникальное ограничение

Answer 2

Словарь полнотекстового поиска, который не учитывает регистр символов

FTS естественно нечувствителен к регистру по умолчанию,

Преобразование токенов в лексемы. Лексема - это строка, похожая на токен, но она была нормализована, так что разные формы одного и того же слова делаются одинаково. Например, нормализация почти всегда включает в себя сведение заглавных букв в строчные и часто включает удаление суффиксов (таких как s или es на английском языке).

И вы можете определить свой собственный словарь, используя unaccent,

CREATE EXTENSION unaccent;

CREATE TEXT SEARCH CONFIGURATION mydict ( COPY = simple );
ALTER TEXT SEARCH CONFIGURATION mydict
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

Который вы можете затем индексировать с помощью функционального индекса,

-- Just some sample data...
CREATE TABLE myTable ( myCol )
  AS VALUES ('fóó bar baz'),('qux quz');

-- No index required, but feel free to create one
CREATE INDEX ON myTable
  USING GIST (to_tsvector('mydict', myCol));

Теперь вы можете запросить его очень просто

SELECT *
FROM myTable
WHERE to_tsvector('mydict', myCol) @@ 'foo & bar'

    mycol    
-------------
 fóó bar baz
(1 row)

Смотрите также

• Создание нечувствительного к регистру и нечувствительного к акценту / диакритике поиска в поле