Accent-insensitive and fuzzy matching: unaccent and fuzzystrmatch

conceptThe concept

unaccent is a text search dictionary plus a function: unaccent('Crème brûlée') returns Creme brulee. Use it in two places: as a filter dictionary in a text search configuration, so to_tsvector removes accents, and in expression indexes for LIKE and equality searches.

The unaccent(text) function is STABLE, not IMMUTABLE, because it looks up its dictionary through the search_path. Indexes need immutable expressions, so the standard pattern is a small IMMUTABLE wrapper that names the schema and dictionary explicitly.

fuzzystrmatch compares strings:

whyWhy it matters

Exact matching fails real users: accents, typos and spelling variants hide records that exist. Customer support search, deduplication of contacts and address matching all need tolerant comparison, and doing it in SQL keeps it close to the data and its indexes.

Use caseReal-world scenario

A support tool searches customers by name. The query unaccents and lowercases input, tries an indexed prefix match first, then falls back to dmetaphone equality on surnames and levenshtein <= 1 on a small candidate set, ranking exact matches above sound-alikes.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE EXTENSION unaccent;
CREATE EXTENSION fuzzystrmatch;
SELECT unaccent('Crème brûlée à São Paulo') AS plain;
SELECT levenshtein('Hyderabad', 'Hyderbad') AS edits,
       soundex('Robert') AS robert, soundex('Rupert') AS rupert,
       metaphone('Thompson', 10) AS metaphone,
       dmetaphone('Schmidt') AS dmetaphone;
ROLLBACK;

BEGIN;
CREATE EXTENSION unaccent;
CREATE FUNCTION f_unaccent(text) RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
  RETURN public.unaccent('public.unaccent'::regdictionary, $1);
CREATE TABLE city (name text);
INSERT INTO city VALUES ('São Paulo'), ('Zürich'), ('Kraków'), ('Montréal'), ('Sao Tome');
CREATE INDEX city_unaccent ON city (lower(f_unaccent(name)) text_pattern_ops);
SELECT name FROM city WHERE lower(f_unaccent(name)) LIKE 'sao%' ORDER BY name;
ROLLBACK;

BEGIN;
CREATE EXTENSION unaccent;
CREATE TEXT SEARCH CONFIGURATION simple_unaccent (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION simple_unaccent
  ALTER MAPPING FOR hword, hword_part, word WITH unaccent, simple;
SELECT to_tsvector('simple_unaccent', 'Crème brûlée') AS doc,
       to_tsvector('simple_unaccent', 'Crème brûlée') @@ to_tsquery('simple_unaccent', 'creme') AS matches;
ROLLBACK;

BEGIN;
CREATE EXTENSION fuzzystrmatch;
SELECT DISTINCT split_part(name, ' ', 2) AS surname,
       levenshtein(lower(split_part(name, ' ', 2)), 'patell') AS edits
FROM customers
WHERE levenshtein_less_equal(lower(split_part(name, ' ', 2)), 'patell', 1) <= 1
ORDER BY edits, surname;
SELECT DISTINCT split_part(name, ' ', 2) AS sounds_like_smyth
FROM customers
WHERE dmetaphone(split_part(name, ' ', 2)) = dmetaphone('Smyth');
ROLLBACK;

SELECT name, round(similarity(name, 'Ananya Patell')::numeric, 2) AS sim
FROM customers
WHERE name % 'Ananya Patell'
ORDER BY sim DESC, name
LIMIT 3;

internalsUnder the hood

levenshtein is O(n times m) per comparison and cannot use an index, so always narrow candidates first (an indexed phonetic code, a trigram index or a prefix). Phonetic functions are deterministic and immutable, so you can store their output in a generated column or index the expression dmetaphone(surname). soundex, metaphone and dmetaphone are designed for English and ignore or mishandle multibyte characters: unaccent before encoding. unaccent's rules live in unaccent.rules in the server's tsearch_data directory and can be extended with your own file.

Avoid / preferDo this, not that

CREATE INDEX ON customers (unaccent(name));  -- fails: not immutable
-- or: ALTER FUNCTION unaccent(text) IMMUTABLE;

CREATE FUNCTION f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
  RETURN public.unaccent('public.unaccent'::regdictionary, $1);
CREATE INDEX ON customers (lower(f_unaccent(name)));

SELECT * FROM customers
WHERE levenshtein(name, $1) <= 2;   -- runs on every row

SELECT * FROM customers
WHERE name % $1                      -- trigram index narrows candidates
  AND levenshtein_less_equal(name, $1, 2) <= 2;

PitfallsCommon mistakes

• Applying soundex or metaphone to non-English or accented names without unaccent, getting empty or misleading codes.
• Searching with unaccent on the column but not on the user input, or the reverse.
• Using a high levenshtein threshold on short strings: every 3-letter name is within 2 edits of many others.
• Forgetting to rebuild the text search configuration on every database that needs it; configurations are per database.

LimitsWhen not to use it

For long free text, use full-text search or trigrams rather than edit distance. For multilingual name matching at scale or entity resolution with many signals, a dedicated search engine or matching service may be a better fit than SQL functions.

SecuritySecurity notes

Fuzzy search over people's names makes it easier to find records with partial information, which is useful for support staff and risky in public endpoints. Require authentication, rate-limit, cap result counts, and never expose fuzzy lookups over customer data to anonymous users.

ChallengeTry it yourself

BEGIN;
CREATE EXTENSION unaccent;
CREATE EXTENSION fuzzystrmatch;
SELECT DISTINCT city
FROM customers
WHERE levenshtein_less_equal(lower(unaccent(city)), lower(unaccent('Bëngalooru')), 2) <= 2
ORDER BY city;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Full text search
• pg_trgm