> From: Björn Lundin <b.f.lundin@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
givena 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.