3

I'm using postgresql to Full Text Search and I am finding that users will not receive results if there are misspellings.I want to use fuzzy search and full text search together.For example I could not combine Trigram indexes and full text search.

What is the best way to handle misspelt words in Postgres full text search?

d.k
  • 147
  • 1
  • 4
  • 15
  • Why do you want to use full text search if trigram indexes do the trick? – Laurenz Albe Sep 03 '18 at 06:18
  • I have product table and I want to search for 'personel pen' . When I write 'prsonel pen' or 'persnel pn'.., I want to find the 'personal pen' product. – d.k Sep 03 '18 at 06:44
  • You can do that with a trigram index without using full text search, right? – Laurenz Albe Sep 03 '18 at 06:50
  • Yes I can use only trigram index but if i do this ,I just can not find the pen. – d.k Sep 03 '18 at 06:59
  • Why not? Can you explain in some more detail? – Laurenz Albe Sep 03 '18 at 07:24
  • select * from product where katadi % 'pencil ' ->is working but select * from product where katadi % 'pncil ' -> not working select * from product where katadi % 'prsnel pen' -> not working select * from product where katadi % ' pen' -> not working select * from product where katadi % ' pers' -> not working – d.k Sep 03 '18 at 07:59
  • Lower `pg_trgm.similarity_threshold`, or better search like this: `SELECT * FROM product ORDER BY katadi <-> ' pen' LIMIT 10`. – Laurenz Albe Sep 03 '18 at 08:05
  • When I wrote it in this way, it worked, would you suggest a source of how to use it in laravel? thank you so much – d.k Sep 03 '18 at 08:37
  • Sorry, I don't know anything about laravel. – Laurenz Albe Sep 03 '18 at 08:39

2 Answers2

1

I'd suggest that you either use full-text search or trigram similarity matching, but don't try to mix them.

Based on the requirement, I would say that trigram similarity matching is the better fit.

If you don't get a result using the similarity operator %, you have two choices:

  1. Lower the similarity threshold pg_trgm.similarity_threshold.

  2. Query in a different way so that you get the best matches, however „distant” they are:

    SELECT * FROM product ORDER BY katadi <-> ' pen' LIMIT 10;

    I think that would be the better solution.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
-1

To use fuzzy search you need to ensure the extension is present.

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

Check the documentation, but you apply the search like;

SELECT levenshtein('GUMBO', 'GAMBOL');

You could put the result into a column to order by perhaps? You should also investigate "regexp_replace" to fix some of your known misspellings

Slumdog
  • 470
  • 2
  • 4