From: George A.J <jinujosein@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Sorting problem
Date: Tue, 14 Oct 2003 20:30:54 -0700 (PDT)
Message-ID: <20031015033054.50872.qmail@web14908.mail.yahoo.com> (raw)
hi all,
i am using postgres 7.3.2 .i am converitng a mssql database to postgres.
now i am facing a strange problem. sorting based on a varchar field is not working
as expected. the non alphanumeric characters are not sorting based on the ascii
value of them.
i have the following table structure..
create table accounts
(
AcNo varchar (10),
Name varchar(100),
balance numeric(19,4)
)
when i used the query select * from accounts order by acno. the result is not correct
suppose that the acno field contains values '###1' ,'###2' ,'##10' , '#100'
the sort order in postgres is
'###1'
'##10'
'#100'
'###2'
But i want the result as follows
'###1'
'###2'
'##10'
'#100'
that means the ascii value of # should be considered for sorting..
what is the problem. is it the behaviour of postgres.
do i need to change any configuration. i am using all default configurations
or is it a bug...?
the problem actually is of < & > operators for varchar.
in a simple comparison
select '###2' < '##10'
returns false but i need true.
is there any solution exist. even if i replaced # with any non alphanumeric
character the result is same..
pls help
jinu jose
---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jinujosein@yahoo.com
Subject: Re: Sorting problem
In-Reply-To: <20031015033054.50872.qmail@web14908.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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