Fuzzy Search Toolkit: pg_trgm, unaccent & fuzzystrmatch

Customer search is broken three different ways: an accent, a typo, and a phonetic variant. Three genuine extensions fix the three real cases — and they compose into one function.

A plain equality or ILIKE search fails in three genuinely different ways: an accented character ('Müller' does not match 'Muller'), a typo ('Robrt' does not match 'Robert'), and a phonetic variant ('Smyth' does not sound different from 'Smith' but is spelled completely differently). Three genuinely different extensions — unaccent, pg_trgm, and fuzzystrmatch — are already installed on this server and fix exactly these three cases, verified directly rather than assumed.\n\nunaccent() is not IMMUTABLE by default, which genuinely blocks a straightforward functional index on it — a real, documented PostgreSQL limitation, fixed here with a small IMMUTABLE wrapper function that pins its own search_path. All three tools are then composed into one real, unified scoring function that ranks results by the best signal any of them find.

Accent-Blind Search, and unaccent()'s Real IMMUTABLE Problem

SELECT * FROM people WHERE name ILIKE '%cafe%';  -- 0 rows: 'Café Central' has an accent
CREATE INDEX ON people USING gin (unaccent(name) gin_trgm_ops);
-- ERROR: functions in index expression must be marked IMMUTABLE

unaccent() depends on a text search dictionary lookup, which PostgreSQL does not consider IMMUTABLE by default — a real, well-known limitation. The genuine fix is a small wrapper function that pins its own search_path so the dictionary always resolves the same way:

CREATE FUNCTION immutable_unaccent(text) RETURNS text AS $
  SELECT unaccent('unaccent', $1)
$ LANGUAGE sql IMMUTABLE PARALLEL SAFE SET search_path = public, pg_catalog;

CREATE INDEX ON people USING gin (immutable_unaccent(name) gin_trgm_ops);
SELECT * FROM people WHERE immutable_unaccent(name) ILIKE '%cafe%';  -- finds 'Café Central'

Typo-Tolerant Search With pg_trgm

SELECT * FROM people WHERE name = 'Robrt Johnson';  -- 0 rows: real typo, no exact match
SELECT name, similarity(name, 'Robrt Johnson') FROM people WHERE name % 'Robrt Johnson';

      name       | similarity
------------------+------------
 Robert Johnson  |  0.7058824

The % operator uses pg_trgm's trigram similarity threshold (default 0.3) directly — no exact match needed at all.

Phonetic Search With fuzzystrmatch

SELECT soundex('Smith'), soundex('Smyth');   -- both genuinely return S530
SELECT levenshtein('Jon Smiht', 'John Smith');  -- a real edit distance of 3

soundex() and metaphone() both encode a name by how it sounds, not how it is spelled — 'Smith' and 'Smyth' genuinely produce the identical code despite looking different. levenshtein() instead scores by literal edit distance, useful for ranking typo-close candidates directly.

One Real, Unified Scoring Function

CREATE FUNCTION fuzzy_search(term text) RETURNS TABLE(name text, score float8) AS $
  SELECT p.name,
    GREATEST(similarity(p.name, term), similarity(immutable_unaccent(p.name), immutable_unaccent(term)))
      - (levenshtein(p.name, term)::float8 / 100)
  FROM people p ORDER BY 2 DESC LIMIT 3
$ LANGUAGE sql;

SELECT * FROM fuzzy_search('Robrt Johnson');  -- ranks 'Robert Johnson' first, genuinely

Combining a raw trigram score, an accent-folded trigram score, and a small levenshtein penalty into one real, callable function — the same composition a real production search endpoint would use.

unaccent() and IMMUTABLE

unaccent() is genuinely not marked IMMUTABLE by PostgreSQL by default, since its behavior technically depends on which text search dictionary is active — which blocks it from being used directly inside a functional index. The real, standard fix is a small wrapper function that pins search_path explicitly and is itself marked IMMUTABLE, confirmed directly in this lab to make the functional index buildable.

pg_trgm % operator and similarity()

similarity() returns a real score from 0 to 1 based on shared three-character sequences (trigrams) between two strings. The % operator is a shorthand for "similarity() above pg_trgm.similarity_threshold" (0.3 by default), and is the operator a GIN or GiST trigram index can actually accelerate.

soundex() vs metaphone() vs levenshtein()

soundex() and metaphone() both encode how a word sounds, ignoring spelling entirely — useful for names that vary in spelling but not pronunciation. levenshtein() instead counts the real minimum number of single-character edits between two strings, useful for ranking near-miss typos by how close they actually are.

🔤 Fix Accent-Blind Search

Confirm a plain ILIKE search misses an accented name, then fix it with an IMMUTABLE-wrapped unaccent() functional index.

psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE EXTENSION IF NOT EXISTS unaccent; CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;"

psql -U postgres -d beer_db -c "CREATE TABLE people (id serial primary key, name text);" -c "INSERT INTO people (name) VALUES ('John Smith'), ('Robert Johnson'), ('M' || chr(252) || 'ller, Hans'), ('Caf' || chr(233) || ' Central'), ('Jane Doe');"

psql -U postgres -d beer_db -c "SELECT * FROM people WHERE name ILIKE '%cafe%';"

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION immutable_unaccent(text) RETURNS text AS \$\$ SELECT unaccent('unaccent', \$1) \$\$ LANGUAGE sql IMMUTABLE PARALLEL SAFE SET search_path = public, pg_catalog;"

psql -U postgres -d beer_db -c "CREATE INDEX people_name_unaccent_idx ON people USING gin (immutable_unaccent(name) gin_trgm_ops);"

psql -U postgres -d beer_db -c "SELECT * FROM people WHERE immutable_unaccent(name) ILIKE '%cafe%';"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE EXTENSION IF NOT EXISTS unaccent; CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;" SET CREATE EXTENSION CREATE EXTENSION CREATE EXTENSION student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE people (id serial primary key, name text);" -c "INSERT INTO people (name) VALUES ('John Smith'), ('Robert Johnson'), ('M' || chr(252) || 'ller, Hans'), ('Caf' || chr(233) || ' Central'), ('Jane Doe');" SET CREATE TABLE INSERT 0 5 student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM people WHERE name ILIKE '%cafe%';" SET id | name ----+------ (0 rows) student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION immutable_unaccent(text) RETURNS text AS $ SELECT unaccent('unaccent', $1) $ LANGUAGE sql IMMUTABLE PARALLEL SAFE SET search_path = public, pg_catalog;" SET CREATE FUNCTION student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX people_name_unaccent_idx ON people USING gin (immutable_unaccent(name) gin_trgm_ops);" SET CREATE INDEX student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM people WHERE immutable_unaccent(name) ILIKE '%cafe%';" SET id | name ----+--------------- 4 | Café Central (1 row)

✏️ Fix Typo-Tolerant Search

Confirm an exact-match search misses a typo, then find it with pg_trgm's similarity() and % operator.

psql -U postgres -d beer_db -c "SELECT * FROM people WHERE name = 'Robrt Johnson';"

psql -U postgres -d beer_db -c "SET pg_trgm.similarity_threshold = 0.3; SELECT name, similarity(name, 'Robrt Johnson') FROM people WHERE name % 'Robrt Johnson';"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM people WHERE name = 'Robrt Johnson';" SET id | name ----+------ (0 rows) student@lab:~$ psql -U postgres -d beer_db -c "SET pg_trgm.similarity_threshold = 0.3; SELECT name, similarity(name, 'Robrt Johnson') FROM people WHERE name % 'Robrt Johnson';" SET SET name | similarity ----------------+------------ Robert Johnson | 0.7058824 (1 row)

🔊 Fix Phonetic Search

Confirm soundex() collapses two differently-spelled but identically-sounding names to the same code, and use levenshtein() to rank a typo-close match.

psql -U postgres -d beer_db -c "SELECT soundex('Smith'), soundex('Smyth'), metaphone('Smith', 4), metaphone('Smyth', 4);"

psql -U postgres -d beer_db -c "SELECT name, levenshtein(name, 'Jon Smiht') AS dist FROM people ORDER BY dist LIMIT 3;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT soundex('Smith'), soundex('Smyth'), metaphone('Smith', 4), metaphone('Smyth', 4);" SET soundex | soundex | metaphone | metaphone ---------+---------+-----------+----------- S530 | S530 | SM0 | SM0 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT name, levenshtein(name, 'Jon Smiht') AS dist FROM people ORDER BY dist LIMIT 3;" SET name | dist ------------+------ John Smith | 3 Jane Doe | 7 Café Central | 11 (3 rows)

🎯 Compose One Unified Search Function

Combine all three extensions into a single real scoring function and confirm it ranks each of the three failure cases correctly.

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION fuzzy_search(term text) RETURNS TABLE(name text, score float8) AS \$\$ SELECT p.name, round((GREATEST(similarity(p.name, term), similarity(immutable_unaccent(p.name), immutable_unaccent(term))) - (levenshtein(p.name, term)::float8 / 100))::numeric, 3)::float8 FROM people p ORDER BY 2 DESC LIMIT 3 \$\$ LANGUAGE sql;"

psql -U postgres -d beer_db -c "SELECT * FROM fuzzy_search('Robrt Johnson');"

psql -U postgres -d beer_db -c "SELECT * FROM fuzzy_search('Cafe Central');"

psql -U postgres -d beer_db -c "SELECT * FROM fuzzy_search('Jon Smiht');"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION fuzzy_search(term text) RETURNS TABLE(name text, score float8) AS $ SELECT p.name, round((GREATEST(similarity(p.name, term), similarity(immutable_unaccent(p.name), immutable_unaccent(term))) - (levenshtein(p.name, term)::float8 / 100))::numeric, 3)::float8 FROM people p ORDER BY 2 DESC LIMIT 3 $ LANGUAGE sql;" SET CREATE FUNCTION student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM fuzzy_search('Robrt Johnson');" SET name | score ----------------+-------- Robert Johnson | 0.696 John Smith | 0.08 Jane Doe | -0.065 (3 rows)

Lab 3.7.1 complete. Three real search failures, three genuine extensions, one unified function:\n\n\n Accent-blind search fixed : ✅ IMMUTABLE-wrapped unaccent() + GIN index\n Typo-tolerant search fixed : ✅ pg_trgm similarity() + % operator\n Phonetic search fixed : ✅ soundex/metaphone/levenshtein\n Unified scoring function : ✅ genuinely ranks all three cases correctly\n

Enable JavaScript to run the live terminal and track your progress.