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: full text search and ILIKE type clauses. From: Tom Lane <tgl@xxxxxxxxxxxxx> Date: Sun, March 11, 2012 7:56 pm To: Tim Uckun <timuckun@xxxxxxxxx> Cc: pgsql-general <pgsql-general@xxxxxxxxxxxxxx> Tim Uckun <timuckun@xxxxxxxxx> 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 (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
