How to make middle and suffix matching using full text search?

Question

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?

score 7 · Answer 1 · answered Oct 23 '17 at 19:17

7

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

answered Oct 23 '17 at 19:17

Neil McGuigan

8,423
4
39
56

1

but it does search prefixes. to_tsquery('pre:*'). – ps2goat Aug 06 '20 at 23:20

score 2 · Answer 2 · answered Oct 23 '17 at 15:50

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 ~~*).

answered Oct 23 '17 at 15:50

jjanes

39,726
3
37
48

Based on Fast Search Using PostgreSQL Trigram Indexes 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 '17 at 08:43
Did you VACUUM ANALYZE after building the index? What was the argument to the LIKE? What was the "explain analyze"? – jjanes Oct 24 '17 at 16:59
EXPLAIN ANALYZE SELECT name FROM users where name like '%ada%' – Slim Oct 25 '17 at 07:33

How to make middle and suffix matching using full text search?

2 Answers2