sorting goofiness

From: Hal Roberts <hroberts(at)cyber(dot)law(dot)harvard(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: sorting goofiness
Date: 2002-04-01 18:50:17
Message-ID: 1017687017.3622.23.camel@ldesk.law.harvard.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm experiencing some goofiness with the order by clause on postgres.
I'm running the 7.2 install from redhat:

****
[hroberts(at)h2o h2o]$ rpm -q postgresql-server
postgresql-server-7.2-1PGDG
****

When I try to sort by a text column, I get odd results:

****
h2o_hroberts=# select ''''||foo||'''' as foo from bar order by foo;
foo
-----------------------
'a'
'a a'
'aaa'
'a b'
'ab '
'abb'
'a l'
'a l'
'alyxbqu lynscw gshi'
'a n'
'a n'
'an'
'a nkp'
'ann'
'a o'
'b'
'bb b'
'bbbb'
(18 rows)
****

"foo" is just a text field. The determination of whether a string with
a space at a given location should be sorted before a string with
non-space at the same location seems to depend on a number of variables
including the total length of the string and the character following the
space. In my elementary school career, I learned that a space should
always sort before a non-space.

Am I missing something here ?

-hal

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ricardo Vaz Mannrich 2002-04-01 18:56:59 err: backend closed the channel unexpectedly
Previous Message Steve Atkins 2002-04-01 18:13:02 substring indices / array operators