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
>
