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.

Accepted Answer

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.

Tsquery return exact matched keyword

Solution

Problem