Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9E5GVr56597 for ; Sun, 14 Oct 2001 01:16:31 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f9E5GRI26146; Sun, 14 Oct 2001 01:16:27 -0400 (EDT) To: speedboy cc: pgsql-sql@postgresql.org Subject: Re: Ordering by field using lower() In-reply-to: References: Comments: In-reply-to speedboy message dated "Sun, 14 Oct 2001 13:45:02 +1000" Date: Sun, 14 Oct 2001 01:16:27 -0400 Message-ID: <26143.1003036587@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/170 X-Sequence-Number: 5125 speedboy writes: > Hi, please view the output from some queries below. My question is, why is > the ordering apparently wrong when using the lower() function? You're getting a textual sort, not a numeric sort. > The field "pid" is of type int2 and there is no need to use lower(), but I > will need to put a test clause in php if my order type is pid so I do not > use lower() in the query. That would be a good plan anyway. It's pure luck for you that there is an implicit int2->text coercion path that allows the text function lower() to be applied to an int2 field. If your app expects to be able to apply lower() to any datatype at all, I'd say your app is broken. regards, tom lane