Re: ORDER BY is case insensitive

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
>