The fuzzystrmatch extension in PostgreSQL provides functions for fuzzy string matching. It is useful for approximate string comparisons, spell-checking, and searching similar words in a database. Your Nile database arrives with fuzzystrmatch extension already enabled.

​
Available Functions

The fuzzystrmatch extension provides several functions for different types of string matching algorithms:

​
Soundex

The soundex() function returns a four-character Soundex code based on how a word sounds:

SELECT soundex('example');

​
Difference

The difference() function compares two Soundex codes and returns a similarity score from 0 to 4 (higher means more similar):

SELECT difference('example', 'exampel');

​
Levenshtein Distance

The levenshtein() function computes the edit distance (number of single-character edits required to transform one string into another):

SELECT levenshtein('example', 'exampel');

​
Levenshtein Distance with Custom Costs

You can specify different costs for insertions, deletions, and substitutions:

SELECT levenshtein('example', 'exampel', 1, 2, 1);

​
Metaphone

The metaphone() function returns a phonetic representation of a word, useful for English-language fuzzy searches:

SELECT metaphone('example', 10);

​
Example: Finding Similar Names

If you have a table with names and want to find names similar to a given input:

CREATE TABLE contacts (
    tenant_id UUID,
    name TEXT,
    email EMAILADDR,
    PRIMARY KEY (tenant_id, email)
);

-- Create a tenant first
INSERT INTO tenants (id, name) VALUES 
    ('11111111-1111-1111-1111-111111111111', 'Example Corp');

INSERT INTO contacts (tenant_id, name, email) VALUES
    ('11111111-1111-1111-1111-111111111111', 'John', 'john@example.com'),
    ('11111111-1111-1111-1111-111111111111', 'Jon', 'jon@example.com'),
    ('11111111-1111-1111-1111-111111111111', 'Johnny', 'johnny@example.com'),
    ('11111111-1111-1111-1111-111111111111', 'Jonathan', 'jonathan@example.com');
    

SELECT name FROM contacts WHERE difference(name, 'Jon') > 2;

​
Use Cases

  • Finding similar names in a customer database.
  • Detecting typos in text input.
  • Enhancing search functionality with approximate string matching.

​
Limitations

  • Soundex is optimized for English and may not work well for other languages.
  • Levenshtein distance can be computationally expensive for large datasets.
  • Phonetic matching may not always align perfectly with intended pronunciations.

​
Conclusion

The fuzzystrmatch extension provides multiple methods for fuzzy string matching, making it a valuable tool for approximate searches and typo detection in PostgreSQL databases.

For more details, refer to the PostgreSQL documentation.