pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY is case insensitive
6+ messages / 5 participants
[nested] [flat]
* ORDER BY is case insensitive
@ 2010-06-22 20:52 Bryan White <nicktook@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Bryan White @ 2010-06-22 20:52 UTC (permalink / raw)
To: pgsql-sql
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)
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY is case insensitive
@ 2010-06-22 21:32 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Bryan White <nicktook@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Gerardo Herzig @ 2010-06-22 21:32 UTC (permalink / raw)
To: Bryan White <nicktook@gmail.com>; +Cc: pgsql-sql
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
------
B
a
b
c
(4 rows)
Or similar...Wich order are you expecting to see?
HTH
Gerardo
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY is case insensitive
@ 2010-06-22 21:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bryan White <nicktook@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2010-06-22 21:40 UTC (permalink / raw)
To: Bryan White <nicktook@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY is case insensitive
@ 2010-06-23 13:45 Jasen Betts <jasen@xnet.co.nz>
parent: Bryan White <nicktook@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Jasen Betts @ 2010-06-23 13:45 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY is case insensitive
@ 2010-06-24 04:49 silly sad <sad@bankir.ru>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 6+ messages in thread
From: silly sad @ 2010-06-24 04:49 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY is case insensitive
@ 2010-06-24 17:26 Bryan White <nicktook@gmail.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 0 replies; 6+ messages in thread
From: Bryan White @ 2010-06-24 17:26 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
> regression=# SELECT * from test order by ascii(data);
That is what I was looking for. Thanks to all.
--
Bryan White
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-06-24 17:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-22 20:52 ORDER BY is case insensitive Bryan White <nicktook@gmail.com>
2010-06-22 21:32 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2010-06-24 17:26 ` Bryan White <nicktook@gmail.com>
2010-06-22 21:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-23 13:45 ` Jasen Betts <jasen@xnet.co.nz>
2010-06-24 04:49 ` silly sad <sad@bankir.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox