Skip site navigation (1) Skip section navigation (2)

Ordering problem with varchar (DESC) - from general ml.

From: "Alexandre Leclerc" <alexandre(dot)leclerc(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Ordering problem with varchar (DESC) - from general ml.
Date: 2007-01-31 16:32:09
Message-ID: 1dc7f0e30701310832r2da685d4ifd858f34373af84a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2007-01-31 16:44:11
Subject: Re: Ordering problem with varchar (DESC) - from general ml.
Previous:From: Seham hammad mohamedDate: 2007-01-31 15:35:35
Subject: Postgresql driver name

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group