Fuzzy String Matching with fuzzystrmatch
In PostgreSQL, a one-character difference between strings like “hired” and “fired” can change the meaning of a query entirely. The fuzzystrmatch extension, part of the PostgreSQL contrib package, provides algorithms to handle such near-matches without requiring exact string equality.
To demonstrate, start with a small sample table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
postgres=# CREATE TABLE t_sample (x text); CREATE TABLE postgres=# INSERT INTO t_sample VALUES ('hired'), ('fired'), ('pump'), ('dump'), ('failure'); INSERT 0 5 postgres=# TABLE t_sample; x --------- hired fired pump dump failure (5 rows) |
Soundex Encoding
After enabling the extension, the first tool available is Soundex, which encodes words into a phonetic representation:
|
1 2 |
postgres=# CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION |
|
1 2 3 4 5 6 7 8 9 |
postgres=# SELECT x, soundex(x) FROM t_sample; x | soundex ---------+--------- hired | H630 fired | F630 pump | P510 dump | D510 failure | F460 (5 rows) |
Because Soundex groups similar-sounding words under the same code, both "fired" and "firred" produce identical encodings—something a plain equality search would miss. The soundex function being immutable allows it to back a functional index:
|
1 2 3 4 5 6 7 |
postgres=# SELECT x, soundex(x) FROM t_sample WHERE soundex(x) = soundex('firred'); x | soundex -------+--------- fired | F630 (1 row) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
postgres=# CREATE INDEX ON t_sample (soundex(x)); CREATE INDEX postgres=# SET enable_seqscan TO off; SET postgres=# explain SELECT x, soundex(x) FROM t_sample WHERE soundex(x) = soundex('firred'); QUERY PLAN --------------------------------------------------- Index Scan using t_sample_soundex_idx on t_sample (cost=0.13..8.15 rows=1 width=64) Index Cond: (soundex(x) = 'F630'::text) (2 rows) |
On larger tables this index can be used directly by the planner; the small sample table above is too small to trigger an index scan unless you force the planner with a runtime flag.
Levenshtein Distance
The extension also implements Levenshtein distance, which counts the minimum single-character edits required to convert one string into another. For example:
|
1 2 3 4 5 |
postgres=# SELECT levenshtein('venture capital', 'adventure game'); levenshtein ------------- 8 (1 row) |
The longer the distance, the more dissimilar the strings. You can filter query results by a tolerance threshold for typos:
|
1 2 3 4 5 6 7 8 |
postgres=# SELECT x FROM t_sample WHERE levenshtein(x, 'pumpy') <= 2; x ------ pump dump (2 rows) |
In that case, two rows fall within the allowed edit distance. Levenshtein is most effective when you expect minor spelling errors and have a fairly clear target in mind.
Metaphone for Length-Controlled Encoding
For more flexibility than Soundex, metaphone allows you to set the maximum length of the phonetic key:
|
1 2 3 4 5 |
postgres=# SELECT metaphone('capital structure', 4); metaphone ----------- KPTL (1 row) |
|
1 2 3 4 5 |
postgres=# SELECT metaphone('capital structure', 6); metaphone ----------- KPTLST (1 row) |
The same approach applies—encode both your search term and the column content, then compare:
|
1 2 3 4 5 6 |
postgres=# SELECT * FROM t_sample WHERE metaphone('open source', 3) = metaphone(x, 3); x --- (0 rows) |
Here, no rows match because metaphone correctly determines that "open source" does not sound like "fired."
Beyond fuzzystrmatch
Community extensions such as pg_similarity offer additional fuzzy search strategies, while techniques for case-insensitive pattern matching and optimizing LIKE/ILIKE queries address other related needs. Those approaches are useful complements to the phonetic and edit-distance functions covered here.
