Postgres Pro
Downloads
Home   >   mailing lists

Thread: ORDER BY is case insensitive

ORDER BY is case insensitive

From
Bryan White
Date:
I was suprised to find out that ORDER BY is case insensitive.  Is
there a way to do a case sensitive ORDER BY clause?

This transcript demonstrates what I am seeing:

$ createdb bryan
$ psql bryan
psql (8.4.4)
Type "help" for help.

bryan=# create table t (f text);
CREATE TABLE
bryan=# insert into t (f) values ('a');
INSERT 0 1
bryan=# insert into t (f) values ('b');
INSERT 0 1
bryan=# insert into t (f) values ('c');
INSERT 0 1
bryan=# insert into t (f) values ('B');
INSERT 0 1
bryan=# select * from t order by f;f
---abBc
(4 rows)

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/postgrestemplate1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                             :
 
postgres=CTc/postgres
(4 rows)



-- 
Bryan White

Re: ORDER BY is case insensitive

From
Gerardo Herzig
Date:
Bryan White 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?
> 
> This transcript demonstrates what I am seeing:
> 
> $ createdb bryan
> $ psql bryan
> psql (8.4.4)
> Type "help" for help.
> 
> bryan=# create table t (f text);
> CREATE TABLE
> bryan=# insert into t (f) values ('a');
> INSERT 0 1
> bryan=# insert into t (f) values ('b');
> INSERT 0 1
> bryan=# insert into t (f) values ('c');
> INSERT 0 1
> bryan=# insert into t (f) values ('B');
> INSERT 0 1
> bryan=# select * from t order by f;
>  f
> ---
>  a
>  b
>  B
>  c
> (4 rows)

Well. Im not really surprised. The column is text, so it sound
reasonable to order by its *text* representation. You may want to order
from its *ascii* value instead:

regression=# SELECT * from test order by ascii(data);data
------Babc
(4 rows)

Or similar...Wich order are you expecting to see?

HTH
Gerardo

Re: ORDER BY is case insensitive

From
Tom Lane
Date:
Bryan White <nicktook@gmail.com> writes:
> I was suprised to find out that ORDER BY is case insensitive.  Is
> there a way to do a case sensitive ORDER BY clause?

Text sort order is determined by the rules of the locale you're using,
specifically the database's LC_COLLATE setting.  Most implementations of
en_US locale use "dictionary" ordering.  You might prefer C locale's
rules instead.
        regards, tom lane

Re: ORDER BY is case insensitive

From
Jasen Betts
Date:
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
>

Re: ORDER BY is case insensitive

From
silly sad
Date:
On 06/23/10 17:45, Jasen Betts wrote:
> 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.
>
>   try this:
>
>   select * from t order by replace(f,e'\\', e'\\\\')::bytea
>
> you may want to index on  replace(f,e'\\', e'\\\\')::bytea

certainly wrong way.
keyword is "multibyte chars".

as he asked about case sensitivity, i presume he still want to preserve 
alphabet order -- u propose to cancel alphabet order at all.

Re: ORDER BY is case insensitive

From
Bryan White
Date:
> regression=# SELECT * from test order by ascii(data);

That is what I was looking for.  Thanks to all.


-- 
Bryan White