String comparision in PostgreSQL

Nicola Cisternino <ncister@xxxxxxxxxx> · Tue, 28 Aug 2012 16:46:55 +0200



    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.