problem with sorting using 'ORDER BY' when character field is filled with numerical values

[Dragan Matic <mlists@panforma.co.yu>]

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

Dragan




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org