How can I search rows that match search phrase? If search query is funny dog
I need to get result, where searching field contains ONLY these 2 words “funny” and “dog” in any order, in any word form (dogs, funnier, funniest…) and skipping prepositions and articles (a, the, and, of…)
Advertisement
Answer
Considering that the english stemmer which PostgreSQL uses does not stem comparatives and superlatives (I have to think that that is by design and not just by mistake) there is no reasonable way to do this in PostgreSQL out of the box.
select to_tsvector('english','funny funnier funniest big bigger biggest');
to_tsvector
-------------------------------------------------------------------
'big':4 'bigger':5 'biggest':6 'funni':1 'funnier':2 'funniest':3
But if you install the english_hunspell dictionary (not itself trivial) then you can get closer to what you want, at least for the one example you give.
create text search configuration english_hun (copy = english);
ALTER TEXT SEARCH CONFIGURATION english_hun alter mapping replace english_stem with english_hunspell;
select to_tsvector('english_hun','funny funnier funniest big bigger biggest');
to_tsvector
----------------------------------------------
'big':4 'bigger':5 'biggest':6 'funny':1,2,3
The most common prepositions are already stop words, but not all of them so you might need to tweak your stop-word list.
After doing the usual match (@@) you could than add an AND condition making sure the length of the tsvector and tsquery are the same. This would match ‘funny dog funny’, which it is not clear to me if you want or not.