What is full text search?
In a full-text search, a search engine examines all of the words in every stored document as it tries to match search criteria (for example, text specified by a user). Full-text-searching techniques became common in online bibliographic databases in the 1990s.
What is to Tsvector in PostgreSQL?
Postgres has a data type called tsvector that is used for full text search. A tsvector value merges different variants of the same word and removes duplicates to create a sorted list of distinct words called lexemes. Those numbers next to the lexemes are their position in the original lyrics.
How does full text search in PostgreSQL work?
So, when you search for a word like “jump”, you will match all instances of the word and its tenses, even if you searched for “jumped” or “jumping”. Additionally, you won’t be searching the full document itself (which is slow), but the vector (which is fast). That is, in a nutshell, the principle of full-text search. Let’s see how it works.
What is the <-> operator in PostgreSQL 9.6?
To address this issue, PostgreSQL 9.6 introduced the <-> operator to further enhance tsquery by allowing you to search for word proximity or distance. Let’s see how it works with some examples.
When to use the like keyword in PostgreSQL?
For example, you may want to search if the nouns “dog” and “fox” are present so if they are in their singular form, you’ll find them with the LIKE keyword… but you’ll also find stuff like “foxtrot” or “Dogville”, which is not quite what you intended.
Is the word jumping false in PostgreSQL search?
That’s false because the search is smart enough not to match anything that simply starts with fox unless it’s related to the same semantics (meaning) of the text originally vectorized; which is good because a fox (the animal) has little to do with foxtrot (the dance)! And finally, now with “jumping”: