Re: String comparision in PostgreSQL

On Wed, Aug 29, 2012 at 10:56 AM, Nicola Cisternino <ncister(at)tiscali(dot)it> wrote:
> Il 29/08/2012 17.08, Merlin Moncure ha scritto:
>
> On Tue, Aug 28, 2012 at 9:46 AM, Nicola Cisternino <ncister(at)tiscali(dot)it>
> wrote:
>
> Hi all,
> I'm valutating a complex porting of our application based on Sybase
> SqlAnywhere on PostgreSQL (I've love it ...) and I'd like to have your
> opinion about searching/ordering funcionality.
> The problem is about string comparision.
> MS Sql server, MySql, SqlAnywhere and other DB engine allow a simple
> definition of case sensitive/insensitive behavior using char, varchar and
> text field type.
> In PostgreSQL I've already tried to use "citext", lower() function (applied
> to indexes, too ...), ILIKE an so on ..... but nothing really work as I need
> (poor performances ...) !!
>
> hm, poor performance? can you elaborate?
>
> merlin
>
> The same query using " .... LIKE <value> ...." is completed in 15 ms while
> using " .... ILIKE <value> ...." the execution time is 453 ms ....

citext unfortunately doesn't allow for index optimization of LIKE
queries, which IMNSHO defeats the whole purpose. to the best way
remains to use lower()

create table foo(f text);
create index on foo(lower(f));
select * from f where lower(f) = 'abc%'

this will be index optimized and fast as long as you specified C
locale for your database.

merlin