PostgreSQL: Native fuzzy search with levenshtein() — First published in fullweb.io issue #41

fuzzy-search-postgres.md

PostgreSQL: Fuzzy search with levenshtein()

Ever wanted to implement a “Did you mean?” feature in your search results? Google is said to have greatly increased its user engagement with it. Here is how to implement it simply in Postgres (v9.1+):

Install the extension:

CREATE EXTENSION fuzzystrmatch;

Test the function:

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

Build your query:

Here only asking the top 5 close matches:

SELECT word
FROM my_table
WHERE levenshtein(word, "user query") <= 3
ORDER BY levenshtein(word, "user query")
LIMIT 5;

BONUS: Use levenshtein_less_equal() for faster lookup:

SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
 levenshtein_less_equal
------------------------
           3
                  

you have to install first the extension 'fuzzystrmatch'