Re: unexpected (to me) sorting order

8.4.2015, 12:09, Björn Lundin kirjoitti:
> 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;
>
>
> bnl=> select version();
>                                                     version
> --------------------------------------------------------------------------------------------------------------
>   PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit
> (1 row)
>
> psql says
> psql (9.3.5, server 9.3.3)
> It is an Amazon RDS-service
>
>
> client machine
>
> bnl@prod:~$ uname -a
> Linux prod 3.2.0-4-amd64 #1 SMP Debian 3.2.63-2+deb7u1 x86_64 GNU/Linux
>
> bnl@prod:~$ locale
> LANG=en_US.UTF-8
> LANGUAGE=
> LC_CTYPE="en_US.UTF-8"
> LC_NUMERIC="en_US.UTF-8"
> LC_TIME="en_US.UTF-8"
> LC_COLLATE="en_US.UTF-8"
> LC_MONETARY="en_US.UTF-8"
> LC_MESSAGES="en_US.UTF-8"
> LC_PAPER="en_US.UTF-8"
> LC_NAME="en_US.UTF-8"
> LC_ADDRESS="en_US.UTF-8"
> LC_TELEPHONE="en_US.UTF-8"
> LC_MEASUREMENT="en_US.UTF-8"
> LC_IDENTIFICATION="en_US.UTF-8"
> LC_ALL=
>
>
> --
> /Björn

Add some more letters lower/upper and so on. Then compare sorting ex.
ö/z. Or look my some ex. 0/!/letter order with or without other chars.

insert into T_SORT values ( 10,'FINISH_Z');
insert into T_SORT values ( 11,'FINISH_a');
insert into T_SORT values ( 12,'FINISH_b');
insert into T_SORT values ( 13,'FINISH_A');
insert into T_SORT values ( 14,'FINISH_B');
insert into T_SORT values ( 15,'FINISH_ä');
insert into T_SORT values ( 16,'FINISH_Ä');
insert into T_SORT values ( 17,'FINISH_+');
insert into T_SORT values ( 18,'FINISH_@');
insert into T_SORT values ( 19,'FINISH_=');
insert into T_SORT values ( 20,'FINISH_]');
insert into T_SORT values ( 21,'FINISH_a0a');
insert into T_SORT values ( 22,'FINISH_a!a');
insert into T_SORT values ( 23,'FINISH_!');
insert into T_SORT values ( 24,'FINISH_012');
insert into T_SORT values ( 25,'FINISH_0aa');
insert into T_SORT values ( 26,'FINISH_!aa');
insert into T_SORT values ( 27,'FINISH_0');
insert into T_SORT values ( 28,'FINISH_!b!b');
insert into T_SORT values ( 29,'FINISH_a!b');
insert into T_SORT values ( 30,'FINISH_b!a');
insert into T_SORT values ( 31,'FINISH_!ab');
insert into T_SORT values ( 32,'FINISH_!b!a');


select * from T_SORT order by NAME ; -- use your db LC_COLLATE

-- using COLLATE = you have installed those locales in
-- your system, PG use those.

select * from T_SORT
ORDER BY name COLLATE "en_US" ;

select * from T_SORT
ORDER BY name COLLATE "fi_FI" ;

select * from T_SORT
ORDER BY name COLLATE "C" ; -- sorting weight = ascii value - simple

select * from T_SORT
ORDER BY name COLLATE "POSIX" ;

select * from T_SORT
ORDER BY name COLLATE "de_DE" ;

Sorting - it's not so easy ... but with COLLATE option you can "fix"
your order if you need / as you want - almost.

http://en.wikipedia.org/wiki/ISO_14651
http://en.wikipedia.org/wiki/European_ordering_rules
http://standards.iso.org/ittf/PubliclyAvailableStandards/index.html
- ISO/IEC 14651:2011/Amd 1:2012
https://www.debian.org/doc/manuals/intro-i18n/ - how the library works
http://en.wikipedia.org/wiki/Internationalization_and_localization
...
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/localeCompare
...


-jukka-