Re: unexpected (to me) sorting order

> select * from T_SORT order by NAME ;
>
> rollback;
> id | name
> ----+--------------------
> 1 | FINISH_110_150_1
> 2 | FINISH_110_200_1
> 3 | FINISH_1.10_20.0_3
> 4 | FINISH_1.10_20.0_4
> 5 | FINISH_1.10_30.0_3
> 6 | FINISH_1.10_30.0_4
> 7 | FINISH_120_150_1
> 8 | FINISH_120_200_1
> (8 rows)

Hi,

PostreSQL relies on the OS's C lib. So this kind
of ordering problems depend on the OS' idea about
collations.

I get the exact same order on 9.4.1 running on Centos 7.1:

chris=# select * from T_SORT order by NAME ;
id | name
----+--------------------
1 | FINISH_110_150_1
2 | FINISH_110_200_1
3 | FINISH_1.10_20.0_3
4 | FINISH_1.10_20.0_4
5 | FINISH_1.10_30.0_3
6 | FINISH_1.10_30.0_4
7 | FINISH_120_150_1
8 | FINISH_120_200_1
(8 rows)

But I get this on 9.3.5 running on OS X 10.8

chris=# select * from T_SORT order by NAME ;
id | name
----+--------------------
3 | FINISH_1.10_20.0_3
4 | FINISH_1.10_20.0_4
5 | FINISH_1.10_30.0_3
6 | FINISH_1.10_30.0_4
1 | FINISH_110_150_1
2 | FINISH_110_200_1
7 | FINISH_120_150_1
8 | FINISH_120_200_1

with both databases having Collate = en_US.UTF-8.

If I put your data in a file and use the command sort
from the shell I get the same effect (this is on
the Centos 7.1 box):

[chris(at)mercury ~]$ cat x
FINISH_1.10_20.0_3
FINISH_1.10_20.0_4
FINISH_1.10_30.0_3
FINISH_1.10_30.0_4
FINISH_110_150_1
FINISH_110_200_1
FINISH_120_150_1
FINISH_120_200_1

[chris(at)mercury ~]$ sort x
FINISH_110_150_1
FINISH_110_200_1
FINISH_1.10_20.0_3
FINISH_1.10_20.0_4
FINISH_1.10_30.0_3
FINISH_1.10_30.0_4
FINISH_120_150_1
FINISH_120_200_1
[chris(at)mercury ~]$

I don't know what's the rationale behin this,
but it looks like Linux ignores the . when doing the sort.

Bye,
Chris.