From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Dragan Matic <mlists(at)panforma(dot)co(dot)yu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with sorting using 'ORDER BY' when character |
Date: | 2004-05-20 16:08:48 |
Message-ID: | 20040520090623.I15535@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 20 May 2004, Dragan Matic wrote:
> If I have a table t with column c which is defined as char(5) and fill
> it with following values:
>
> insert into t (c) values (' 1')
> insert into t (c) values (' 2')
> insert into t (c) values (' 3')
> insert into t (c) values (' 4')
> insert into t (c) values (' 11')
> insert into t (c) values (' 12')
> insert into t (c) values (' 14')
> insert into t (c) values (' 24')
> insert into t (c) values (' 21')
> insert into t (c) values (' 31')
> insert into t (c) values (' 333')
>
> and then do the following: SELECT C FROM T ORDER BY C
> Postgres gives me the following
>
> 1
> 11
> 12
> 14
> 2
> 21
> 24
> 3
> 31
> 333
> 4
>
> the same thing done with MS SQL server gives this as a result:
>
> 1
> 2
> 3
> 4
> 11
> 12
> 14
> 21
> 24
> 31
> 333
>
> which is the result I find more logical, meaning the user would expect
> data sorted this way. Is there some way to make Postgres sort elements
> in this way (setting sort order or collation, I suppose)? Tnx in advance
You are probably running in a collation that doesn't treat spaces as
particularly significant (for example with a locale of en_US). If you
want collation by byte order you can use "C" locale (although you need to
re-initdb to change it).
You could also sort it as numbers by converting to a numeric type first
presumably.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-05-20 16:10:38 | Re: problem with sorting using 'ORDER BY' when character field is filled with numerical values |
Previous Message | Dave Smith | 2004-05-20 15:26:20 | Re: problem with sorting using 'ORDER BY' when character |