Installing and Using FuzzyStrMatch

Out of the box, PostgreSQL has some amazing extensions that are free to install and use for fuzzy matching strings which go beyond the abilities of the text search functionality in PostgreSQL. Using these extensions puts the capabilities of fuzzy matching on strings within SQL queries.

First, it is necessary to install the fuzzystrmatch contribution on the PostgreSQL host. On Ubuntu, this is as simple as:

sudo apt-get install postgresql postgresql-contrib

Next, to make the contributions accessible to this PostgreSQL instance, it is necessary to restart PostgreSQL.

The third step is to create the extension by issuing create extension fuzzystrmatch. This could be done within psql or PGAdmin as shown below.

create extenstion fuzzystrmatch within PGAdmin

At this point, there are ten new functions available to the database: difference, dmetaphone, dmetaphone_alt, levenshtein (two versions), levenshtein_less_equal (again, two versions), metaphone, soundex and text_soundex.

This simple example illustrates using the levenshtein function:

select levenshetin('foo', 'foobar');

...and results in the answer 3, for there are three edits necessary to transform 'foo' into 'foobar'.

This example SQL replicates the functionality of the GMSStrComp I wrote in Java to provide a percentage value for the similarity of two strings as explained in depth in the Levenshtein Fuzzy String Match post:

Select 1.0 - (levenshtein('foo', 'foobar')::float /
              greatest(length('foo'), length('foobar')))

Which results in the expected value of 0.5, hence 50% matching; which is exactly how much 'foo' matches 'foobar'.