Re: problem with sorting using 'ORDER BY' when character

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.