Quick summary
Summarize this blog with AI
Fuzzy matching proposes candidates; it does not prove identity
A small edit distance can flag a typo, but it can also connect two genuinely different names. This PostgreSQL 16 tutorial uses the fuzzystrmatch extension’s Levenshtein function, declares its prerequisites, measures a small candidate set, and retains ambiguity. No records are merged or deleted.
Separate normalization, candidate generation, and scoring
Normalization applies an approved representation rule, such as trimming outside spaces and lowercasing a case-insensitive business name. Candidate generation restricts which pairs are compared. Scoring measures a property of those candidate strings. A final decision still needs evidence beyond spelling: a verified identifier, address, country, or human review. Keep original strings and provenance alongside every proposed match.
Our fixture blocks candidates by an exact country code, then compares normalized names. That rule reduces comparisons but can miss true matches whose country is wrong or has changed. Blocking is a recall decision, not merely a performance optimization. The example uses country codes as supplied and does not claim that they are independently verified.
Install the declared extension only in a practice database
The script expects PostgreSQL 16 with fuzzystrmatch extension files available and permission to create the extension. It checks the installation schema, runs two comparisons, and rolls back. A production owner should manage extension prerequisites. This is not a generic LEVENSHTEIN function guaranteed to exist on every SQL engine.
BEGIN;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;
DO $$ BEGIN
IF (SELECT extnamespace::regnamespace::text FROM pg_extension
WHERE extname='fuzzystrmatch')<>'public' THEN
RAISE EXCEPTION 'This fixture expects fuzzystrmatch in public';
END IF;
END $$;
WITH pairs(label,a,b) AS (
VALUES ('insertion','Jon','John'),('transposition','Jhon','John'),
('accent','Jose','José'),('different-name','John','Joan')
)
SELECT label,public.levenshtein(a,b) AS edits FROM pairs ORDER BY label;
WITH incoming(id,name,country) AS (
VALUES (1,' Acme North ','US'),(2,'Muller','DE'),(3,'Cafe','FR')
), reference(id,name,country) AS (
VALUES (10,'Acme Noth','US'),(11,'Acme North','CA'),(12,'Müller','DE'),
(13,'Cafe','FR'),(14,'Cafe','CA'),(15,'Acme North','US')
), candidates AS (
SELECT i.id AS incoming_id,r.id AS reference_id,
public.levenshtein(LOWER(BTRIM(i.name)),LOWER(BTRIM(r.name))) AS edits
FROM incoming i JOIN reference r ON r.country=i.country
), scored AS (
SELECT *,COUNT(*) OVER(PARTITION BY incoming_id) AS candidate_count,
COUNT(*) FILTER(WHERE edits<=1) OVER(PARTITION BY incoming_id) AS within_threshold
FROM candidates
)
SELECT incoming_id,reference_id,edits,candidate_count,within_threshold
FROM scored ORDER BY incoming_id,edits,reference_id;
ROLLBACK;label | edits
accent | 1
different-name | 1
insertion | 1
transposition | 2
incoming_id | reference_id | edits | candidate_count | within_threshold
1 | 15 | 0 | 2 | 2
1 | 10 | 1 | 2 | 2
2 | 12 | 1 | 1 | 1
3 | 13 | 0 | 1 | 1Interpret the score before choosing a threshold
Standard Levenshtein counts insertions, deletions, and substitutions. Swapping adjacent characters in Jhon to obtain John costs two under this metric; it is not the one-edit transposition rule of a Damerau-style metric. Jose versus José costs one, while John versus Joan also costs one. The latter illustrates why distance one is not proof that the records describe the same entity.
The six reference rows and three incoming rows would produce 18 unrestricted pairs. Exact country blocking produces four candidate pairs: two for input 1, one for input 2, and one for input 3. All four fall within the illustrative distance-one threshold. Input 1 still has two candidates, even though one is an exact normalized match. The result ranks evidence for review; it does not automatically merge either record.
Document language and Unicode assumptions
Trimming and lowercasing do not remove accents or establish transliteration equivalence. Unicode normalization, punctuation removal, and token reordering each change what counts as equal; preserve originals and justify the rule against labeled examples. Phonetic algorithms also have language assumptions. Do not promise that a Soundex match establishes that two multilingual names are the same person.
The PostgreSQL extension limits Levenshtein inputs to 255 characters. Validate length before calling it on arbitrary text; silently truncating a long name can create false matches. Similarity measures for trigrams are provided by a different extension, pg_trgm, with its own functions and indexes. A made-up generic TRIGRAM_SIMILARITY name is not portable SQL. See fuzzystrmatch and pg_trgm.
Measure candidates and inspect the plan
Comparing every row with every other row grows quadratically. Measure the candidate count before applying expensive scoring, and test blocking rules on labeled true matches to quantify what they miss. An ordinary B-tree on name does not generally accelerate an arbitrary Levenshtein predicate. An equality index on a selective blocking field may help candidate generation, while trigram indexes support their documented operators. Confirm behavior in an actual plan.
Limit the reviewed candidate list deliberately, but do not confuse a top-N cutoff with a confidence score. Ties need stable ordering and an ambiguity flag. A short string often needs a different threshold from a long one; an absolute edit count has different meaning for Li and for a long organization name.
Evaluate decisions with labeled pairs
Prepare known matches and known non-matches, including typographical, accent, transposition, and common-name cases. Report false positives and false negatives for candidate generation separately from scoring. Choose a threshold based on the cost of each error and keep uncertain cases for review. A matching system should explain which rule proposed a link and how a mistaken link can be reversed.
Retain uncertainty instead of fabricating a match
The useful output is a candidate list with source IDs, normalization rules, distances, and ambiguity counts. Exact identity evidence can justify a link; spelling similarity alone usually cannot. The fixture demonstrates scoring arithmetic and reduced comparison counts, not production matching accuracy.
FAQ
Is an exact normalized name always unique? No; different entities can share it. Should fuzzy candidates be deleted as duplicates? No; resolve identity and authority before any cleanup. Does a lower distance always mean a correct match? It means strings are closer under this metric, not that their real-world identities agree.
