Question 387403 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    "Off" is an English stop word, so it is removed by to_tsquery() with the English text search configuration.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search