Postgres in depth · Chapter 59 of 65
"Did you mean React?"
By the end of this chapter you can find text that is nearly right, rank suggestions by how close they are, and make LIKE '%...%' searches fast with a trigram index. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Extensions and graduation
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Kabir watched a student type reakt into LCO's course search and get an empty page. "Google would have said did you mean React?" Sana has the same problem with support tickets that misspell names, and Priya has a different one: the admin page's name search, name ILIKE '%sha%', reads the whole table every time.
Full-text search (chapter 58) needs the right words; a typo is not a word it knows. levenshtein (chapter 57) forgives typos but compares every row, with no index. pg_trgm does both: it measures how similar two strings are, and it gives LIKE, ILIKE and similarity searches an index.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
suggest.sql: one suggestion per typed search
Students typed these searches: kubernets, pyhton, javascrpt, flutr and dokcer. For each one, suggest the published course whose title contains the best match for it, by word similarity (the typed text compared with the best matching part of the title). Suggest only when that score is at least 0.5; a search with no good enough match has no row.
Return three columns: term (as typed), title and score (the word similarity, rounded to 3 decimal places). Order by term. When two titles score the same for a term, suggest the one that comes first alphabetically.
Install pg_trgm in the file if it is missing: the Check runs it in a fresh copy of LCO's database.
- Task 2
close-names.sql: names close to a ticket's spelling
A ticket is signed Myra Sha. Find the students (not deleted) whose name is similar to that spelling by pg_trgm's similarity operator, with its default threshold.
Return two columns: name and score (the similarity of the name and Myra Sha, rounded to 3 decimal places). Order by score from highest, then name.
Install pg_trgm in the file if it is missing.
- Task 3
name-index.sql: a fast search inside names
Priya's admin page searches names with a pattern that starts with a wildcard, like students whose name contains sha in any case. Add an index on students.name that such a search can use.
Install pg_trgm in the file if it is missing.
Afterwards: students has a trigram index on name, and a search with a leading wildcard can use it.
- Task 4 · interview question
Interview: trigram-math.sql, a similarity score by hand
An interviewer asks how pg_trgm's similarity is computed. Show it for the pairs postgres and postgis, and typescript and javascript.
Return five columns: word_a, word_b, shared (how many distinct trigrams the two words have in common), in_either (how many distinct trigrams are in at least one of them) and similarity (pg_trgm's similarity of the two, rounded to 4 decimal places). Order by word_a.
Install pg_trgm in the file if it is missing.
What you will learn
- Trigrams: how pg_trgm cuts text into three-letter pieces.
- similarity and %: a score from 0 to 1, and a threshold.
- word_similarity and <%: a short search inside a longer title.
- Trigram indexes: GIN for ILIKE '%...%', GiST for nearest matches with <->.
More in Extensions and graduation
Build ""Did you mean React?"" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.