> From: Björn Lundin <b.f.lun...@gmail.com> >To: pgsql-general@postgresql.org >Sent: Wednesday, 8 April 2015, 10:09 >Subject: [GENERAL] unexpected (to me) sorting order > > > >Hi! >below are some commands to >replicate a strange sorting order. > >I do not see why id:s 3-6 are in the middle of the result set. > >What am I missing? > > >begin; > >create table T_SORT ( > ID bigint default 1 not null , -- Primary Key > NAME varchar(100) default ' ' not null >); >alter table T_SORT add constraint T_SORTP1 primary key ( > ID >); > > >insert into T_SORT values ( 1,'FINISH_110_150_1'); >insert into T_SORT values ( 2,'FINISH_110_200_1'); >insert into T_SORT values ( 3,'FINISH_1.10_20.0_3'); >insert into T_SORT values ( 4,'FINISH_1.10_20.0_4'); >insert into T_SORT values ( 5,'FINISH_1.10_30.0_3'); >insert into T_SORT values ( 6,'FINISH_1.10_30.0_4'); >insert into T_SORT values ( 7,'FINISH_120_150_1'); >insert into T_SORT values ( 8,'FINISH_120_200_1'); > >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) > >why is FINISH_1.10_20.0_3 between > FINISH_110_200_1 and > FINISH_120_150_1 >? > >That is why is '.' between 1 and 2 as in 110/120 ? > > >pg_admin III reports the database is created like >CREATE DATABASE bnl > WITH OWNER = bnl > ENCODING = 'UTF8' > TABLESPACE = pg_default > LC_COLLATE = 'en_US.UTF-8' > LC_CTYPE = 'en_US.UTF-8' > CONNECTION LIMIT = -1; > >
The collation of your "bnl" database is utf8, so the "." punctuation character is seen as a "variable element" and given a lower weighting in the sort to the rest of the characters. That's just how the collate algorithm works in UTF8. Try with LC_COLLATE = 'C' and it should sort how you expect. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
