The course "Mastering Postgres" helped me practice query commands and understand why they're important. Inspiring me to explore the 'why' and 'how' behind each one. Truly a great course!Nakolus
Shorten dev cycles with branching and zero-downtime schema migrations.
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. And then 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. So let's start by moving up one level. To move up one level, we're gonna say plainto_tsquery. So instead of just to_tsquery, which is what we've been doing, we're saying plainto_tsquery. And we can say, let's look at this. Start Wars. Star Wars. And if we run that, you'll see great. It converted it to that little esoteric syntax for us. So if we bring back, hopefully we can just, let's just undo, undo, perfect. There we go. So let's drop out the rank 'cause that's not what we're talking about right now. And we can come down here and say plainto_tsquery Star Wars. And if we run that, we still get an error 'cause we forgot to remove that. And 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. So kind of 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. So 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. So 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. And now 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. So 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. And 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. And so 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. And so 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. And so 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. So 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. So 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. And in most cases it's going to convert it to something that is reasonable that will return valid results for your end users. So 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.