pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Ordering problem with varchar (DESC) - from general ml. 2+ messages / 2 participants [nested] [flat]
* Ordering problem with varchar (DESC) - from general ml. @ 2007-01-31 16:32 Alexandre Leclerc <alexandre.leclerc@gmail.com> 2007-01-31 16:44 ` Re: Ordering problem with varchar (DESC) - from general ml. Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Alexandre Leclerc @ 2007-01-31 16:32 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Ordering problem with varchar (DESC) - from general ml. 2007-01-31 16:32 Ordering problem with varchar (DESC) - from general ml. Alexandre Leclerc <alexandre.leclerc@gmail.com> @ 2007-01-31 16:44 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2007-01-31 16:44 UTC (permalink / raw) To: Alexandre Leclerc <alexandre.leclerc@gmail.com>; +Cc: pgsql-admin "Alexandre Leclerc" <alexandre.leclerc@gmail.com> writes: > 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; What makes you think that query is using an index? It's probably just returning the data in physical order, which might look correctly ordered if you inserted all the data in time order to start with. > SELECT * from t1 ORDER BY date, time DESC; Perhaps you mean "ORDER BY date DESC, time DESC" ? I don't actually see how a varchar field set up like that would sort in a sensible numeric order at all --- text comparisons are unlikely to do what you'd like with the optional leading one. Consider replacing the varchar with a field of type TIME. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-01-31 16:44 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-01-31 16:32 Ordering problem with varchar (DESC) - from general ml. Alexandre Leclerc <alexandre.leclerc@gmail.com> 2007-01-31 16:44 ` Tom Lane <tgl@sss.pgh.pa.us>
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