Re: full text search and ILIKE type clauses. - Mailing list pgsql-general

Tim Uckun <timuckun@gmail.com> writes:
> I want to be able to search a lot of fields using queries that use
> ILIKE and unfortunately many of the queries will be  using the
> '%SOMETHING%' or '%SOMETHING' type clauses.  Since indexes are useless
> on those I was thinking I could use tsvectors but I can't figure out
> how to accomplish this.

Full text search is not going to help for this unless you are willing to
be very lax about replicating the semantics of ILIKE.  For example,
ILIKE '%foo%' should match "foo" anywhere within a word, but FTS is not
going to be able to do better than finding words that begin with "foo".

If you're using 9.1, you might look into contrib/pg_trgm instead.

            regards, tom lane