Postgres case insensitive searches

From: bhanu udaya <udayabhanu1984(at)hotmail(dot)com>
To: Kevin Grittner <kgrittn(at)mail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org>
Cc: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, Chris Travers <chris(dot)travers(at)gmail(dot)com>, Magnus Hagander <magnus(at)hagander(dot)net>
Subject: Postgres case insensitive searches
Date: 2013-06-28 10:21:33
Message-ID: COL127-W20F6AB97900F1D8ACC8169D3760@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support pgsql-general

Hello,

Grettings,

What is the best way of doing case insensitive searches in postgres using Like.

Ilike - does not use indexes
function based indexes are not as fast as required.
CITEXT - it still taking 600 ms - 1 second on a 2.2 million rows... does not use index
Collation Indexes creation with POSIX - does not really work.
GIST/GIN indexes are faster when using like, but not case insenstive.

Is there a better way of resolving this case insenstive searches with fast retrieval.

Thanks and Regards
Radha Krishna

In response to

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Albe Laurenz 2013-06-28 12:32:00 Re: Postgres case insensitive searches
Previous Message Chris Haley 2013-06-27 21:49:47 removal

Browse pgsql-general by date

  From Date Subject
Next Message Phoenix Kiula 2013-06-28 12:30:02 How to REMOVE an "on delete cascade"?
Previous Message Vincent Veyron 2013-06-28 09:42:20 Re: utf8 errors