Full text search partial search with 'in:*' not working

Question

select to_tsvector('english', 'JAVA') @@ to_tsquery('english','ja:*');

select to_tsvector('english', 'india') @@ to_tsquery('english','in:*');

The first one is returning true while the second is returning false. How to address this?

Answer 1

That is because "in" is a very frequent word in the English language, so it is eliminated as a "stopword".

You could use the simple text search configuration, but I suspect that full text search is not the right tool for this. Trigram indexes work better for substring searches, but nothing will really work well for strings as short as two characters.

Answer 2

If what you have is already a tsquery, then you don't want to pass it through to_tsquery. Just write it directly:

select to_tsvector('english', 'india') @@ 'in:*';

In some cases when the type is not clear from context, you might need to cast it: 'in:*'::tsquery.

Personally I don't get the point of the to_tsquery function. It munges its input either too much, or not enough. I either use plainto_tsquery or its kin, or just write my tsquery directly in its final form.