Thread: sorting goofiness

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

****
[hroberts@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

On 1 Apr 2002, Hal Roberts wrote:

> 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 ?

You're probably using a locale other than C (such as en_US if you're in
the US) which has different sorting rules.  If you put the above data
in a text file and do commands like:
sort file
LANG="C" sort file
LANG="en_US" sort file

You'll see differences in the sort order.