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
                  

What is <= 3?
And why exactly 3?

@akopchinskiy 3 here tells the maximum number of characters changes, to move from "user query" to word

I haven't gotten this to work:
ERROR: function levenshtein(unknown, unknown) does not exist
LINE 1: SELECT levenshtein('GUMBO', 'GAMBOL');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

SQL state: 42883

you have to install first the extension 'fuzzystrmatch'