Re: Order by and strings

Justin Graf wrote:
> On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
>> Hi!
>> New to the list with a question that I cannot find the answer to in
>> the manual or on the internet but I suspect is trivial. If somebody
>> could point me in the correct direction I would be greatful.
>>
>> This is what I do (condensed, of course):
>> # create table tmp ( x text ) ;
>> CREATE TABLE
>> # insert into tmp(x) values ('a'),('c'),('-b') ;
>> INSERT 0 3
>> # select * from tmp order by x ;
>> x
>> ----
>> a
>> -b
>> c
>> (3 rows)
>>
> It has to do with the collation you are using
I use locale en_US.UTF-8 but why this should affect how leading
characters in strings are ignored is beyond me.

Another, in my mind, counter-intuitive example:
# create table tmp ( x text ) ;
# insert into tmp(x) (values
('a'),('-b'),('c'),('aa'),('---a-b'),('ac'),('1'),('-2'),('+3'),('4'),('-'),('+'))
;
# select * from tmp order by x ;
x
--------
-
+
1
-2
+3
4
a
aa
---a-b
ac
-b
c
(12 rows)

In what universe would you expect this sort order? And how to make it
'sane'?
I found a work-around, "order by ascii(x),x", but this continues to
baffle me.

It seems to me that if there are any alphanumeric characters in the
string the rest are ignored in the sort.
Where did this rule come from?
I really would appreciate an explanation for this behavior.

/Fredric

PS. I was wrong about the server version, it is 8.3.8.