> From: Chris Mair <chris@xxxxxxxx> > To: Björn Lundin <b.f.lundin@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx > Cc: > Sent: Wednesday, 8 April 2015, 10:36 > Subject: 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. > > I think this is down to behaviour changes in glibc, there was a thread a while ago where somebody replicating via streaming rep 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@xxxxxxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
