Is tsvector the right choice for searching exact match of two words with variations like plural and singular?

Question

I'm developing a search application that takes a string and then break it down into a combination of two words and then search a table for matching patterns. An example:

'My cat ate a rat'

search patterns: 'my cat' 'cat ate' 'ate a' 'a rat'

At the same time, this should match with plurals and singulars of the combination. So 'my cat' should match 'my cats' and also "my cat's". Stop words should not be ignored and the two words must be in the same order with no words between them.

My question is, is tsvector the right tool for this or can this be done using the 'LIKE' operator alone? I'm asking this to decide whether I need to spend time diving deep into tsvector to build my queries.

Thanks!

note: this is my first time here, please let me know if this is not the right place to ask this type of question.

Answer 1

Short answer; yes, a tsvector column used as an index will allow you to return matching documents with stemming and pluralization handled by the default text search configuration.

The documentation is very complete and easy to read and will give you a solid grounding in how full-text search works and a bit in managing updates to the corpus and likely issue.

There are some subtleties you need to look at, but the defaults are pretty good for most languages. Things that might give issues are mostly to do with your documents structure and quirks that might need to be addressed with tweaks to the dictionaries or stop words list ( stop words are ignored for search purposes ).