From: Tom Lane <tgl@sss.pgh.pa.us>
To: speedboy <speedboy@nomicrosoft.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Ordering by field using lower()
Date: Sun, 14 Oct 2001 01:16:27 -0400
Message-ID: <26143.1003036587@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com>
References: <Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com>
speedboy <speedboy@nomicrosoft.org> 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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, speedboy@nomicrosoft.org
Subject: Re: Ordering by field using lower()
In-Reply-To: <26143.1003036587@sss.pgh.pa.us>
* 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