Postgres in depth · Chapter 57 of 65
Friendlier text
By the end of this chapter you can compare text the way people expect: without caring about capitals, without caring about accents, and close enough to forgive a typo. 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
Sana's inbox is full of the same three complaints. A student signed up as Rohan.Das@Example.com, typed rohan.das@example.com at login and was told the account does not exist. A student on a phone keyboard searched for pýthon and found nothing. And a ticket asks about "Ishan Gupt", a student who does not exist under that spelling.
To Postgres, text is a sequence of characters, and = compares them exactly: 'Neha.Kapoor@LCO.dev' = 'neha.kapoor@lco.dev' is f. ILIKE ignores case but not accents, and nothing you know yet forgives a typo. Three small extensions fix each problem.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
emails.sql: emails that ignore case
Change the column email of students to the citext type, so that a login typed in any mix of capitals finds the student, and the unique constraint refuses the same address in other capitals.
Install citext in the file if it is missing: the Check runs it in a fresh copy of LCO's database.
Afterwards: students.email is citext, it is still unique, and every student keeps their email exactly as it was typed.
- Task 2
search-terms.sql: search that ignores accents
Students typed these four searches on a phone keyboard: Pýthon, RÉACT, básics and Rüst. For each search, find the published courses whose title contains the typed text, ignoring both case and accents.
Return two columns: term (the text as typed) and title. One row per term and matching course; a term that matches nothing has no row. Order by term, then title.
Install unaccent in the file if it is missing.
- Task 3
did-you-mean.sql: the student the ticket meant
A support ticket mentions a student called ishan gupt. Suggest the students (not deleted) whose name is at most 4 edits away from that text, ignoring capitals.
Return two columns: name and edits (the Levenshtein distance between the lowercased name and ishan gupt). Order by edits, then name.
Install fuzzystrmatch in the file if it is missing.
- Task 4 · interview question
Interview: sounds-alike.sql, first names that sound the same
An interviewer asks for pairs of different first names among LCO's students that sound alike: their Double Metaphone codes are the same. The first name is everything before the first space of a student's name; count every student, deleted or not.
Return three columns: first_name, sounds_like and code. Each pair appears once, with first_name the one that comes first alphabetically. Order by first_name, then sounds_like.
Install fuzzystrmatch in the file if it is missing.
What you will learn
- citext: a text type that compares without caring about case.
- unaccent: a function that strips accents before you compare.
- fuzzystrmatch: edit distance (levenshtein) and sound codes (soundex, dmetaphone).
More in Extensions and graduation
Build "Friendlier text" 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.