Re: Order by and strings

[Fredric Fredricson <Fredric.Fredricson@xxxxxxxxxxxxx>]

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.

begin:vcard
fn:Fredric Fredricson
n:Fredricson;Fredric
org:Ln4 Solutions AB
email;internet:Fredric.Fredricson@xxxxxxxxxxxxx
title:CTO
tel;home:+46 8 91 64 39
tel;cell:+46 70 677 58 48
version:2.1
end:vcard

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general