9

I'm trying to find a string on a database using FTS on Postgres:

select name
from users
where to_tsvector('simple', name) @@ to_tsquery('simple', 'ad:*')

But the :* search only the words start with ad.

I found this question Get partial match from Get partial match from GIN indexed TSVECTOR column column.

But it doesn't mention how to solve the problem of middle and suffix matching using FTS?

2 Answers 2

6

The "Full" in Full Text Search means match full words, not parts of words

You should use RegExp or LIKE or tri-gram matching instead

You can mimic suffix matching by reversing words in your index and also reversing your queries, but this takes more space:

select
reverse('brown fox')::tsvector @@ (reverse('rown') || ':*')::tsquery --true
1
  • 1
    but it does search prefixes. to_tsquery('pre:*').
    – ps2goat
    Aug 6, 2020 at 23:20
2

I don't think that that is possible with FTS. It seems like a better job for pg_trgm, which can accelerate like and ilike (and their symbolic forms, ~~ and ~~*).

3
  • Based on [Fast Search Using PostgreSQL Trigram Indexes ](about.gitlab.com/2016/03/18/…) I tried to use pg_trgm on a table containig 12165 rowsand the result give me a response time shorter when I call like operator without the index but when invoking an index I got a time longer than the first.
    – Slim
    Oct 24, 2017 at 8:43
  • Did you VACUUM ANALYZE after building the index? What was the argument to the LIKE? What was the "explain analyze"?
    – jjanes
    Oct 24, 2017 at 16:59
  • EXPLAIN ANALYZE SELECT name FROM users where name like '%ada%'
    – Slim
    Oct 25, 2017 at 7:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.