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.


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

  • Your question is not very clear. Which "search patterns" are supposed to yield hits and which do not? The need to handle plurals makes it a better match for FTS than for LIKE. – jjanes Apr 9 at 12:21
  • The search patterns mentioned in the code section should yield a hit. The combination of two words like 'my cat' and like 'cat ate'. but at the same time the some variations like plural 'my cats' and 'cats ate' should yield a hit too as long as the two words are in the same order with no other word between them. – tito.300 Apr 9 at 15:15

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 ).

  • Thanks Larry! I just wanted a confirmation that tsvector was the right choice before investing the time learning the ins and outs to structure my queries. – tito.300 Apr 10 at 3:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.