> From: Chris Mair <chris@1006.org>
> To: Björn Lundin <b.f.lundin@gmail.com>; pgsql-general@postgresql.org
> Cc:
> Sent: Wednesday, 8 April 2015, 10:36
> Subject: Re: [GENERAL] 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.
>
>
I think this is down to behaviour changes in glibc, there was a thread a while ago where somebody replicating via
streamingrep between with different versions of glibc ended up in a bit of a pickle.
http://www.postgresql.org/message-id/BA6132ED-1F6B-4A0B-AC22-81278F5AB81E@tripadvisor.com