SQL from zero · Chapter 35 of 37

A search box

By the end of this chapter you can search posts by their words, rank the matches, index the search, and find people by a name with typos. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Fast
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Meera wants a search box at the top of every page. Type a word, get the posts that use it, best match first. Type a name, get the people, even if you misspell it.

With what you know, the search would be ILIKE '%night%'. That matches letters, not words:

SELECT count(*) FILTER (WHERE content ILIKE '%night%') AS ilike_night,
       count(*) FILTER (WHERE to_tsvector('english', content) @@ to_tsquery('english', 'night')) AS fts_night
FROM posts WHERE deleted_at IS NULL;
 ilike_night │ fts_night
─────────────┼───────────
          15 │         3
(1 row)

ILIKE also finds "tonight" and "overnight": 12 wrong posts. It cannot say which match is best, it reads every row, and "vikrm rao" finds nobody.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    word-post.sql: posts that use the word post

    Find the posts that are not deleted and use the word post in any form (post, posts, posted), but not words that only start with those letters, like Postgres. Use full-text search with the english configuration.

    Return two columns: username (the post's author) and content. Any order.

  2. Task 2

    ranked-search.sql: cricket or chai, best match first

    A member typed cricket or chai into the search box. Find the posts that are not deleted and match it, reading it the way a person types it (websearch_to_tsquery, english configuration). rank is ts_rank of the post's english words against that search, rounded to 4 decimals.

    Return two columns: content and rank. Best rank first; when two posts have the same rank, the newest post first.

  3. Task 3

    search-index.sql: a GIN index for the search

    Create an index on posts that the full-text search on content can use. The searches turn content into words with the english configuration, to_tsvector('english', content), and match a query against them. Full-text search needs a GIN index, on exactly that expression.

    Afterwards, the plan of such a search names your index.

  4. Task 4 · interview question

    Interview: people-search.sql, names similar to rao

    Someone typed rao into the people search. Find the members who are not deleted whose display_name is similar to rao by trigrams (the % operator of pg_trgm). score is the similarity of display_name to rao, rounded to 2 decimals.

    Return three columns: username, display_name and score. Most similar first; when two members are equally similar, sort them by username A to Z.

What you will learn

  • Full-text search: to_tsvector, a tsquery and @@, so a search finds words in any form.
  • Ranking: ts_rank scores each match, so the best come first.
  • A GIN index on the search expression, so a search does not read every post.
  • Trigrams (pg_trgm): similarity and % for names with typos.

More in Fast

Build "A search box" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.