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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.