Search code examples
postgresqlfull-text-search

Postgres: ts_vector @@ ts_query not working as expected with wildcard ('searchterm:*')

I am trying to construct a full text search where half words should be matched. In my example, the column value is 'offer' and the search term is 'off'. I construct the query like this. The first line does NOT return the row, but the second line does.

SELECT * FROM my_table WHERE to_tsvector(my_column) @@ to_tsquery('off:*')
SELECT * FROM my_table WHERE to_tsvector(my_column) @@ to_tsquery('offe:*')

in both cases, the term passed to to_tsquery should match the column value ('offer').

Now, I tried to simplify this by hardcoding the value of to_tsvector:

SELECT 1 WHERE to_tsvector('offer') @@ to_tsquery('offe:*') -> returns 1
SELECT 1 WHERE to_tsvector('offer') @@ to_tsquery('off:*') -> Does not return anything.

This does not make any sense to me, because both 'offe' and 'off' are contained by 'offer'. Can anyone explain that behaviour to me?

Solution

  • to_tsquery still applies stemming and stop words, and "off" is a stop word in English, so it gets removed. (having removed the only lexeme in the string, it will then publish a NOTICE, but if you don't process NOTICEs on the client I guess you wouldn't know that.)

    To bypass stemming and stop words, you could enter the tsquery directly:

    SELECT 1 WHERE to_tsvector('offer') @@ 'off:*'::tsquery;

    Of course if you use a stemming (or stop word) using configuration along with partial matching, then you are living in a state of sin.