Search⌘ K
Home
Courses
The Art of PostgreSQL

Complete and Suggest Song Titles

Explore how to implement trigram-based similarity searches in PostgreSQL to handle user typos effectively. Learn to suggest relevant song titles by limiting results to the closest matches, improving search functionality and user experience.

We'll cover the following...

Show the suggestions

What if the search string is being mistyped? We all make typos, and our users will too. Let’s try it with a small typo: peas.

PostgreSQL
select artist, title
  from lastfm.track
 where title ~* 'peas';

This query returns no rows! It seems our Last.fm selection of titles doesn’t include the famous “Pass The Peas” by Maceo Parker. Anyway, our users will not be very happy with zero results, and I’m sure they would like to see suggestions for results.

So instead, we could use the following similarity query:

PostgreSQL
select artist, title
  from lastfm.track
 where title %> 'peas';

And now, here’s a list of song titles that have trigrams similar to the trigrams of our search string:

 ...