pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / 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>
  2001-10-14 05:16 ` Re: Ordering by field using lower() Tom Lane <tgl@sss.pgh.pa.us>
  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 03:45 Ordering by field using lower() speedboy <speedboy@nomicrosoft.org>
@ 2001-10-14 05:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  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