Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL: Native fuzzy search with levenshtein() — First published in fullweb.io issue #41

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
                  
@akopchinskiy
Copy link

akopchinskiy commented Jul 5, 2019

What is <= 3?
And why exactly 3?

@nepsilon
Copy link
Author

nepsilon commented Jul 5, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment