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 <[email protected]> Date: Sun, March 11, 2012 7:56 pm To: Tim Uckun <[email protected]> Cc: pgsql-general <[email protected]> Tim Uckun <[email protected]> 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 -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
