String comparision in PostgreSQL

From: Nicola Cisternino <ncister(at)tiscali(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: String comparision in PostgreSQL
Date: 2012-08-28 14:46:55
Message-ID: 503CD9DF.40808@tiscali.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ...) !!
My questions are:
1) Why PostgreSQL don't use COLLATE to manage case sensitive /
insensitive comparision (I think it's the best and ANSI standard way ....) ?
2) Can I build a custom COLLATION (for example named: "NOCASE" ....) to
apply to my DB objects ? What's the way (... some example ? ) ???

Thanks.
Best Regards.
Nicola.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2012-08-28 14:47:02 Re: "Need some information about postgresql products and community"
Previous Message Tom Lane 2012-08-28 14:30:09 Re: 9.2 and index only scans