Re: full text search and ILIKE type clauses.

Tom,

We made most of our text, varchar columns citext data types so that we
could do case insensitive searches. Is this going to negate most of the
index searches? It appeared to our DBA that it would be easier to use
citext data type then need to use ILIKE instead?

Michael Gould
Intermodal Software Solutions, LLC
904-226-0978


-------- Original Message --------
Subject: Re: [GENERAL] full text search and ILIKE type clauses.
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Sun, March 11, 2012 7:56 pm
To: Tim Uckun <timuckun(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>

Tim Uckun <timuckun(at)gmail(dot)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