Skip to content

Instantly share code, notes, and snippets.

@nepsilon
Last active December 13, 2023 14:16
Show Gist options
  • Save nepsilon/383bb294234579048bba to your computer and use it in GitHub Desktop.
Save nepsilon/383bb294234579048bba to your computer and use it in GitHub Desktop.
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

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

@jrdunson2
Copy link

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

@TheXHeli
Copy link

you have to install first the extension 'fuzzystrmatch'

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