To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: ORDER BY is case insensitive
Date: 23 Jun 2010 13:45:22 GMT
Organization: Dis (not Dat) Organisation
Lines: 49
Message-ID: <hvt35i$p4j$1@reversiblemaps.ath.cx>
References: <AANLkTimupyTgU-h-LKkV9VprzFopMMBKqsoO2RVpAzgL@mail.gmail.com>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: slrn/pre1.0.0-2 (Linux)

On 2010-06-22, Bryan White <nicktook@gmail.com> wrote:
> I was suprised to find out that ORDER BY is case insensitive.  Is
> there a way to do a case sensitive ORDER BY clause?

use bytea instead of a text type.

> This transcript demonstrates what I am seeing:
>
> bryan=# select * from t order by f;
>  f
> ---
>  a
>  b
>  B
>  c
> (4 rows)

 try this:

 select * from t order by replace(f,e'\\', e'\\\\')::bytea
 
you may want to index on  replace(f,e'\\', e'\\\\')::bytea

 

>
> bryan=# \q
> ~  $ psql -l
>                                      List of databases
>       Name       |  Owner   | Encoding |  Collation  |    Ctype    |
> Access privileges
> -----------------+----------+----------+-------------+-------------+-----------------------
>  bryan           | bryan    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  postgres        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  template0       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>                                                                    :
> postgres=CTc/postgres
>  template1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>                                                                    :
> postgres=CTc/postgres
> (4 rows)
>
>
>
> -- 
> Bryan White
>