pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Ordering by field using lower() 2+ messages / 2 participants [nested] [flat]
* Ordering by field using lower() @ 2001-10-14 03:45 speedboy <speedboy@nomicrosoft.org> 0 siblings, 1 reply; 2+ messages in thread From: speedboy @ 2001-10-14 03:45 UTC (permalink / raw) To: pgsql-sql Hi, please view the output from some queries below. My question is, why is the ordering apparently wrong when using the lower() function? 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. Which is not a problem but I am interested as to why lower() returns a different set of results when being used on int2. Thanks. logs=# select max(pid) from syslogd; max ------- 31924 (1 row) logs=# select min(pid) from syslogd; min ----- 548 (1 row) logs=# select pid from syslogd order by pid asc limit 10; pid ------ 548 548 548 548 548 876 876 3983 3983 3983 (10 rows) logs=# select pid from syslogd order by lower(pid) asc limit 10; pid ------- 25272 25389 26170 26323 29958 30294 30294 30504 30504 31770 (10 rows) logs=# select pid from syslogd order by pid desc limit 10; pid ------- 31924 31924 31924 31924 31924 31923 31923 31923 31923 31922 (10 rows) logs=# select pid from syslogd order by lower(pid) desc limit 10; pid ------ 9088 9088 876 876 548 548 548 548 548 5158 (10 rows) logs=# ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Ordering by field using lower() @ 2001-10-14 05:16 Tom Lane <tgl@sss.pgh.pa.us> parent: speedboy <speedboy@nomicrosoft.org> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2001-10-14 05:16 UTC (permalink / raw) To: speedboy <speedboy@nomicrosoft.org>; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-10-14 05:16 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2001-10-14 03:45 Ordering by field using lower() speedboy <speedboy@nomicrosoft.org> 2001-10-14 05:16 ` 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