So now that we've explored doing some of the stop words and stemming using SQL natively, we're going to do the much easier way of doing this by building on the Postgres capabilities and their stemming and stop words and their ability to store this data very densely far surpasses our ability because they can build indexes specifically for this kind of stuff and they can tweak it and have extensive stop word lists. So really what I just showed you with the docs_gin was just to give you a sense of the kind of basic data structures that Postgres is going to make on our behalf. Now, before we get into how to do it, turns out how to do it's not hard at all. There are two really important functions. So in the previous examples, we were using string_to_array to split strings into words. There is a language-aware function, two functions called ts_vector and ts_ query, that basically take strings and turn them into an internal structure. And so this select to_tsvector, you'll notice that we have to tell it what language it is because then it's picking a dictionary for stemming and stop words and other kinds of tuning. And what that does is it goes and does the stemming and stop words and tells you along with the position the non thrown away stuff. So you'll notice that and is thrown away. And you can see the stemming if we say to_tsquery in teaching. You can see that teaching is stemmed down to teach, just like what we did in the previous example. And so then in the WHERE clause, so the ts_query is a thing you're looking for and ts_vector is a thing that you're indexing. Okay? And this function to_tsvector and to_tsquery converts using a language to either a ts_vector or a ts _query object unless ts_query doesn't have a position in there. These are the positions. Also is twice in positions two and six, Python is in fourth position, SQL's in seventh position, teaches. That's what those numbers are telling you. We use this at, double at-sign operator which is really is if you think of it, you can put various things on one side but I think of it as like left side ts_query, right-side ts_vector. It returns a true or a false. So let's go ahead and run these commands. Again, I'm using the www.pg4e.com/lectures/05-FullText.sql for the samples, we can run some to_tsvector commands. I'll just copy all three of these to_tsvector commands. So these selects that have no tables, they're just kind of running the functions and you'll see like people's been stemmed, learn. The stop words are all happening. And so there you go. So that's the to_tsvector. ts_query, you can see some stemming going on. Let's grab these next four. I'm going to do a to_tsquery of teaching teaches in English, so teaching as a query is teach, teaches as a query is teach. So that's two examples of stemming. And if we do a to_tsquery of the word and, it complains, "Hey, don't put only stop words." So I put a stop word. And is a common stop word. But you'll also know that and you do a to_tsquery on something like SQL, which it doesn't have a stem for, and it's not a stop word, it just converts it to lowercase and gives it to us. Now again, we worked really hard to build all this in SQL with like five six-line-long SELECT statements, and the to_tsquery does all this for us. So that's quite nice. So you can also put logical operators, the to_tsquery with a vertical bar that's or, or, or, or, or. And so you can think of this ts_query being sent to the database. I wish it would be clever enough to realize that teach or teach or teach or teach. You see that it's stemmed, it's lowercase, it threw away stop words, and it's basically it should really just say teach, but teach or teach or teach. These ts_queries, you can think of them as being sent to the database like, "Hey database, you have all these documents. We're going to hand you this query." It's a parsed query. It's like a compiled query. We're firing it into the database and the database is going to scan the rows or, if all goes well, it'll use the index instead. Now, there's a couple of different ways to do this. There is plain text to_tsquery like in this case plainto_tsquery of SQL and let me grab both of them. This one I don't like so much because this imples all the words happen, it implies and. And, of course, it's doing stemming. But if you do this plainto_tsquery, that implies and between all of the operators. There is a phrase, and that basically says we want phraseto_tsquery says I would want to see SQL followed by Python, and again, stemmed in lowercase. If you are Postgres 11 or later, which I happen to be in this particular one, you can do websearch_to_tsquery. Now, websearch_to_tsquery uses a syntax that I think was pioneered by Google. It's got a couple of special things in there. It also tolerates. It does something if you have like a syntax error, it doesn't freak out and you're kind of expected that this can be typed by the user and not blow the system up. Some of these things like plainto_tsquery or to_tsquery, you can type syntax errors in the query. So you have to be careful, but the phraseto and the websearch_to don't need that. So we can ask whether or not a particular query is inside of a particular text vector. And this is kind of where you're stemming and stop wording. So you have to stem and stop word the query and then you have to stem and stop word the thing that you're querying. So we'll stop here, but then up next, we're going to show how you actually do the index using these functions.
