Mastering Postgres
Introduction
Introduction to the course
Overview of course structure
Postgres vs. everyone
The psql CLI
Data Types
Introduction to schema
Integers
Numeric
Floating point
Storing money
NaNs and infinity
Casting types
Characters types
Check constraints
Domain types
Charsets and collations
Binary data
UUIDs
Boolean
Enums
Timestamps
Timezones
Dates and times
Advanced Data Types
Intervals
Serial type
Sequences
Identity
Network and mac addresses
JSON
Arrays
Generated columns
Text search types
Bit string
Ranges
Composite types
Nulls
Unique constraints
Exclusion constraints
Foreign key constraints
Indexing
Introduction to indexes
Heaps and CTIDs
B-Tree overview
Primary keys vs. secondary indexes
Primary key types
Where to add indexes
Index selectivity
Composite indexes
Composite range
Combining multiple indexes
Covering indexes
Partial indexes
Index ordering
Ordering nulls in indexes
Advanced Indexing
Functional indexes
Duplicate indexes
Hash indexes
Naming indexes
Understanding Query Plans
Introduction to explain
Explain structure
Scan nodes
Costs and rows
Explain analyze
Generating Results
Introduction to queries
Cross joins
Inner joins
Outer joins
Subqueries
Lateral joins
ROWS FROM
Filling gaps in sequences
Subquery elimination
Combining queries
Set generating functions
Indexing joins
Advanced SQL
Introduction to advanced SQL
Grouping
Grouping sets, rollups, cubes
Window functions
CTEs
CTEs with window functions
Recursive CTE
Hierarchical recursive CTE
Handling nulls
Row value syntax
Views
Materialized views
Removing duplicate rows
Upsert
Returning keyword
COALESCE + generated column
Full Text Search
Introduction to full text search
Searching with LIKE
Vectors, queries, and ranks
Websearch
Ranking
Indexing full text search
Highlighting
Advanced JSON
Intro to JSON
JSON vs JSONB
Validating JSON
Creating JSON objects + arrays
JSON extraction
JSON containment
JSON existence
JSON recordset
Updating JSON
Indexing JSON parts
GIN index
Vectors (pgvector)
Intro to pgvector
Vector embedding columns
Find related articles
Upsert vector embedding
Semantic search
Other operators
Vector indexes
Outro
Thank you
Bonus interviews
Heroku's glory days & Postgres vs the world (with Craig Kerstiens)
Creating a Postgres platform with Monica & Tudor from Xata.io
Bootstrapping an email service provider (with Jesse Hanley)
Locked video

Please purchase the course to watch this video.

Video thumbnail
Full Text Search
Websearch

Full Course

$
349
$399
USD, one-time fee
Mastering Postgres is single-handedly the most thorough and informative database course I’ve seen. I’ve gone from someone who barely knew the basics of Postgres and how it works to being comfortable with the advanced topics of Postgres.
Ryan Hendrickson
Ryan Hendrickson

PostgreSQL database platform

Shorten dev cycles with branching and zero-downtime schema migrations.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Xata

Summary

Explore how to enhance search queries in Postgres using `plainto_tsquery`, `phraseto_tsquery`, and `websearch_to_tsquery`. I find `websearch_to_tsquery` particularly useful because it mimics common Google search operators, providing a user-friendly search experience without errors. This approach allows you to use Postgres for full-text search longer before considering switching to other search engines like Typesense or Elasticsearch.

Video Transcript

Now that we understand a little bit of the underlying workings of the TS Query, we can move up a level and hand it a phrase and have it construct a TS query out of that. We're gonna move up even another level and just throw some user input at it, and we know that we're gonna end up okay, which is a really nice feeling.

Let's start by moving up one level. To move up one level, we're gonna say plainto_tsquery. Instead of just to_tsquery, which is what we've been doing, we're saying plainto_tsquery. We can say, let's look at this. Start Wars. Star Wars. If we run that, you'll see great. It converted it to that little esoteric syntax for us. If we bring back, hopefully we can just, let's just undo, undo, perfect. There we go. Let's drop out the rank 'cause that's not what we're talking about right now. We can come down here and say plainto_tsquery Star Wars. If we run that, we still get an error 'cause we forgot to remove that. If we run that, we'll see, great, we do get all of the Star Wars back. There's no good way to say ors here, so you can say Star Trek and get all of the Star Treks back.

Another function that we have at our disposal. The lowest level is plainto. We don't get a lot out of that. The very next level up is phraseto, which honestly is kind of as simple as plainto except that it is, it enforces the strict following rules. If we come in here and we say instead of, instead of saying plainto, if we said phraseto_tsquery, and then we dropped in Star, I always do that, star and Wars here, and we run that, you'll see there's our little, I wanna call it a spaceship operator. There's our little phrase operator. The difference here, let's bring both of these up. Put a comma, change this to wars, and we'll say that this is as phrase, and this is as plain. We can clearly see the difference. The phrase does enforce this strict follow by order, and the plain is just saying, man, just show me anything with star and war in it. We can bring this guy down here. Let's just take that, we can do that. Take that, boom, there we go. And Star Trek.

If we change this to Star War, just one, then we do get all of the Star Wars back because the lexeme is war not necessarily wars. This is all well and good, but frankly at this point I'm bailing. I'm bailing on Postgres for full text search because this is, this is, this is too cumbersome. I can't expose this to an end user and have them type a bunch of stuff in and expect to get a very good result. At this point I'm bailing. I'm going to Typesense, I'm gonna to Meilisearch. I'm going to Elastic. I'm not using Postgres. Fortunately we're not done yet. In addition to plainto and phraseto, there's also websearch_to.

We've been an entire generation has been trained on those Google search operators where if you put things in quotes, it is an exact search, and if you put a little dash, it says don't include this word. We have that capability. Let me show you. Okay, let's make some space here, and we're gonna come up here, and we're gonna say websearch_to which is different in style than plainto and phraseto. But that's fine. We can forgive a little inconsistency. We'll call this as web. And now we're in this world of more traditional Google style operators, which you might be familiar with. Quotes, quotes give us exact match. We have star followed strictly by war, which is the same as the phrase, but we can do something like what's a terrible Star Wars movie? The clone one. If we drop out clone, just using this nice syntax here with the negate, if we drop out Clone, then it's definitely not going to be in there, and we can add clone and that gives us the and operator there.

We can also, if we drop down to just back to Star Wars, we can say Star Wars or Trek and that gives us the pipe operator, which is that or operator. Now one of the great things about this, I think there are two great things about the websearch_to_tsquery. One of them is this is what users expect. This in my opinion is how users are gonna type stuff. They're gonna put quotes around stuff. They're gonna say or. They're gonna put the little minus sign to say don't include that. I think people are trained on that. And if not, it's a very short step to train them versus trying to get them to understand TS Query syntax. The second thing that I think is so great about websearch_to_tsquery is it will not throw an error. If we messed up our to_tsquery like we did at the very beginning, we put a space between star and wars, how could we? We got an error, right? Everything went wrong. Websearch_to_Tsquery, you can literally, you can pipe in the user input, obviously sanitize it, but you can pipe it in.

You don't have to look and see like is this valid? Is this valid Ts query syntax? Don't worry about it. Pipe it in to this websearch_to_Tsquery, and out comes valid Ts query syntax. It may be a little bit mismatched from the user intent, but it's not going to error. In most cases it's going to convert it to something that is reasonable that will return valid results for your end users. In my opinion, websearch_to_Tsquery brings us right back into Postgres full text search. Gives us that nice, that nice usable user experience, and can get us a super long way before we have to reach for a dedicated search engine.