Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 392F99FB1DF for ; Wed, 31 Jan 2007 12:44:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 27452-06 for ; Wed, 31 Jan 2007 12:44:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id A07E49FA36A for ; Wed, 31 Jan 2007 12:44:12 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l0VGiBiV027020; Wed, 31 Jan 2007 11:44:11 -0500 (EST) To: "Alexandre Leclerc" cc: pgsql-admin@postgresql.org Subject: Re: Ordering problem with varchar (DESC) - from general ml. In-reply-to: <1dc7f0e30701310832r2da685d4ifd858f34373af84a@mail.gmail.com> References: <1dc7f0e30701310832r2da685d4ifd858f34373af84a@mail.gmail.com> Comments: In-reply-to "Alexandre Leclerc" message dated "Wed, 31 Jan 2007 11:32:09 -0500" Date: Wed, 31 Jan 2007 11:44:11 -0500 Message-ID: <27019.1170261851@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/298 X-Sequence-Number: 24454 "Alexandre Leclerc" 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