The Art of PostgreSQL/

...

/

Complete and Suggest Song Titles

Complete and Suggest Song Titles

Learn how to use the suggestions to complete the purpose in case of typos.

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.

Press + to interact
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:

Press + to interact
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:

 ...