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@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@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@mercury ~]$

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


Bye,
Chris.