Как создать простой нечеткий поиск только с PostgreSQL?

Postgres предоставляет модуль с несколькими функциями сравнения строк, такими как soundex и metaphone. Но вы захотите использовать функцию редактирования расстояния левенштейна.

Example:

test=# SELECT levenshtein('GUMBO', 'GAMBOL');
 levenshtein
-------------
           2
(1 row)

2 — это расстояние редактирования между двумя словами. Когда вы примените это к ряду слов и отсортируете по результату расстояния редактирования, вы получите тип нечетких совпадений, который вы ищете.

Попробуйте этот пример запроса: (конечно, с вашими именами объектов и данными)

SELECT * 
FROM some_table
WHERE levenshtein(code, 'AB123-lHdfj') <= 3
ORDER BY levenshtein(code, 'AB123-lHdfj')
LIMIT 10

Этот запрос говорит:

Дайте мне первые 10 результатов всех данных из some_table, где расстояние редактирования между значением кода и входным значением «AB123-lHdfj» меньше 3. Вы получите все строки, где значение кода находится в пределах 3 символов разницы с « AB123-lHdfj'...

Примечание: если вы получаете сообщение об ошибке, например:

function levenshtein(character varying, unknown) does not exist

Установите расширение fuzzystrmatch, используя:

test=# CREATE EXTENSION fuzzystrmatch;

Пол рассказал вам о levenshtein(). Это очень полезный инструмент, но он также очень медленный с большими таблицами. Он должен вычислять расстояние Левенштейна от условия поиска для каждой отдельной строки. Это дорого и не может использовать индекс. Ускоренный вариант levenshtein_less_equal() работает быстрее для длинных строк, но все еще медленный без поддержки индекса.

Если ваши требования настолько просты, как показано в примере, вы все равно можете использовать LIKE. Просто замените любое - в поисковом запросе на % в предложении WHERE. Итак, вместо:

WHERE code ILIKE '%AB-123-lHdfj%'

Использовать:

WHERE code ILIKE '%AB%123%lHdfj%'

Или динамически:

WHERE code ILIKE '%' || replace('AB-123-lHdfj', '-', '%') || '%'

% в шаблонах LIKE означает 0-n символов. Или используйте _ ровно для одного символа. Или используйте регулярные выражения для более точного соответствия:

WHERE code ~* 'AB.?123.?lHdfj'

.? ... 0 или 1 символ

Or:

WHERE code ~* 'AB\-?123\-?lHdfj'

\-? ... 0 или 1 тире

Вы можете экранировать специальные символы в шаблонах LIKE или регулярных выражений. Видеть:

• Функция Escape для регулярного выражения или шаблонов LIKE

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

• Конечно, есть полнотекстовый поиск. Но это может быть излишеством в вашем случае.

• Более вероятным кандидатом является сопоставление триграмм с дополнительным модулем pg_trgm. Видеть:

  • Using Levenshtein function on each element in a tsvector?
  • вариации производительности запросов PostgreSQL LIKE
  • Похожая запись в блоге от Depesz

  Его можно комбинировать с LIKE, ILIKE, ~ или ~*, начиная с PostgreSQL 9.1.
  Также интересно в этом контексте: функция similarity() или оператор % этого модуля.

• И последнее, но не менее важное: вы можете реализовать самодельное решение с функцией нормализовать искомые строки. Например, вы можете преобразовать AB1-23-lHdfj --> ab123lhdfj, сохранить его в дополнительном столбце и искать с терминами, преобразованными таким же образом.

  Или используйте индекс выражения вместо избыточного столбца. (Участвующие функции должны быть IMMUTABLE.) Возможно, объедините это с pg_tgrm сверху.

Обзор методов сопоставления с образцом:

• Сопоставление шаблонов с LIKE, SIMILAR TO или регулярными выражениями в PostgreSQL