The first thing we're gonna cover is using the LIKE and the ILIKE operators, and this is not a good way to build a full text search engine, but it is a way to search text. We're gonna cover it very quickly before we move on to tsquery and tsvector.

Coming back to select * from movies. If we take a look at these and I just wanna order by releaseyear desc, 'cause I think I'm always going to want to see that. Let's order by releaseyear desc and let's do releaseyear desc, title desc. Let's do it like that. Okay, so now, we have a pretty stable set of movies here and let's just go ahead and search for, tsk, (sharply inhales) let's search for Oregon.

If we wanted to search for Oregon, just kind of like naively, we could say where title LIKE, and then we could put these parentheses, sorry, these percent marks, and say '%Oregon%', if you can spell Oregon. That's gonna bring back all of the movies where the title contains Oregon. Now, here's the problem. If either the data was entered, the data was entered incorrectly or more likely, the user just says, hey, I just wanna see Oregon, your host, because like is in fact case-sensitive, which is in my opinion, kind of a bummer. But you do have ILIKE. If you have ILIKE there, then you're okay, you're back to a case in sensitive search. This is better than nothing, right?

If you're just searching, for example, like an email column or just even just the title column, you might be able to get away with this. A username, something like that, doing an ILIKE with these are wild cards here. Doing the ILIKE with the wild card is acceptable. Now, once you start getting into, okay, I wanna look in the plot as well, then you start having to do stuff like or plot ilike. You come up here and you say '%Oregon%' as well and see now we're starting, now we're already at half a second or more, and it's just on 35,000 rows. It's just not that big of a table.

You're already starting to feel like this is not a good solution. This is not the search engine solution that we're going to go with. I did wanna show you this, because LIKE and ILIKE can be valuable in other situations. It can be extremely valuable, especially if you wanna say like, hey, this string needs to start or this column needs to start with the string or again, and nobody does. Or it needs to end with the string or again, which there you go, that movie actually and the title actually ends in Oregon. You can use that more likely on a like an email column or a username column. We're gonna move on from this, 'cause this is not really full text search.