I am trying to run a fulltext query using Postgresql that can cater for partial matches using wildcards.
It seems easy enough to have a postfix wildcard after the search term, however I cannot figure out how to specify a prefix wildcard.
For example, I can perform a postfix search easily enough using something like..
SELECT "t1".*
FROM "t1"
WHERE (to_tsvector('simple', "t1"."city") @@ to_tsquery('simple', 'don:*') )
should return results matching "London"
However I cant seem to do a prefix search like...
SELECT "t1".*
FROM "t1"
WHERE (to_tsvector('simple', "t1"."city") @@ to_tsquery('simple', ':*don') )
Ideally I'd like to have a wildcard prefixed to the front and end of the search term, something like...
SELECT "t1".*
FROM "t1"
WHERE (to_tsvector('simple', "t1"."city") @@ to_tsquery('simple', ':*don:*') )
I can use a LIKE condition however I was hoping to benefit from the performance of the full text search features in Postgres.
Full text search is good for finding words, not substrings.
For substring searches you'd better use like '%don%'
with pg_trgm
extension available from PostgreSQL 9.1 and using gin (column_name gin_trgm_ops)
or using gist (column_name gist_trgm_ops)
indexes. But your index would be very big (even several times bigger than your table) and write performance not very good.
There's a very good example of using pg_trgm for substring search on select * from depesz blog.