Tsquery return exact matched keyword

Question

I have a query like

select * from mytable where posttext @@ to_tsquery('Intelence');

I just want to return results with exact match of the keyword 'Intelence' rather than 'intel', how can I do this in postgresql?

Thanks.

Answer 1

This is not possible with full-text search unless you want to tell PostgreSQL not to stem Intelence at all by changing the text search dictionary. Pg doesn't include the word in the index, only the stems:

regress=> SELECT to_tsvector('english', 'Intelence');
 to_tsvector 
-------------
 'intel':1
(1 row)

You can suppress stemming entirely with the simple dictionary:

regress=> SELECT to_tsvector('simple','Intelence');
  to_tsvector  
---------------
 'intelence':1
(1 row)

but that must be done on the index, you can't do it per-query let alone per search term. So the text cats are bothering me would not match a search for cat in the simple dictionary because of the plural, or bother because the unstemmed words are not the same.

If you want to make individual exceptions you can edit the english dictionary used by tsearch2 and define a custom dictionary with the desired changes, then use that dictionary instead of english in queries where you want the exceptions. Again, you must use the same dictionary for the index creation and the queries, though.

This might land up with you needing multiple fulltext indexes, which is very undesirable from the point of view of slowing down updates/inserts/deletes and from a memory use efficiency perspective.