Re: ORDER BY is case insensitive

On 2010-06-22, Bryan White <nicktook(at)gmail(dot)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
>