Re: [GENERAL] Order by and strings

[Fredric Fredricson]

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.
<<attachment: Fredric.Fredricson.vcf>>-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general