SQL Academy · Lesson

Trigram Search (pg_trgm) for Fuzzy Matching

Find similar strings with the pg_trgm extension, GiST/GIN trigram indexes, and similarity().

Why Trigrams?

LIKE %word% can't use a B-tree index. Trigrams ("3-character substrings") combined with GIN/GiST give you fast fuzzy and substring search.

pg_trgm ships with PostgreSQL contrib:

CREATE EXTENSION IF NOT EXISTS pg_trgm;