Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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
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 ~~*).
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.