From: Alexandre Leclerc <alexandre.leclerc@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Ordering problem with varchar (DESC) - from general ml.
Date: Wed, 31 Jan 2007 11:32:09 -0500
Message-ID: <1dc7f0e30701310832r2da685d4ifd858f34373af84a@mail.gmail.com> (raw)
Hi all,
I don't want to double post, but I see nothing hapening in the general
mailling list, so I post here in case any one has an idea about what
is going on.
We have a column (varchar) that has plain text time and it is indexed.
When I do a query with the index, all the data is in the right order,
but when I user ORDER BY .. DESC, the order is messed up. Example:
By index 1: (date, time, data)
SELECT * from t1;
date (date type) time (varchar) data
2007-01-17 8h40 d1
2007-01-30 9h30 d2
2007-01-30 12h00 d3
2007-01-30 13h45 d4
2007-01-30 17h20 d5
SELECT * from t1 ORDER BY date, time DESC;
date (date type) time (varchar) data
2007-01-30 9h30 d2
2007-01-30 17h20 d5
2007-01-30 13h45 d4
2007-01-30 12h00 d3
2007-01-17 8h40 d1
I don't know why, this is like if the 'time' varchar was trimmed then
used for the ordering.
How can I fix that so that the result is exactly like the first one but
perfectly reversed in it's order?
Best regards.
--
Alexandre Leclerc
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: alexandre.leclerc@gmail.com
Subject: Re: Ordering problem with varchar (DESC) - from general ml.
In-Reply-To: <1dc7f0e30701310832r2da685d4ifd858f34373af84a@mail.gmail.com>
References: <1dc7f0e30701310832r2da685d4ifd858f34373af84a@mail.gmail.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