Re: unexpected (to me) sorting order

9.4.2015, 15:43, Glyn Astill kirjoitti:
>  > From: Scott Marlowe <scott.marlowe@gmail.com>
>  > To: Glyn Astill <glynastill@yahoo.co.uk>
>  > Cc: Björn Lundin <b.f.lundin@gmail.com>;
> "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
>  > Sent: Thursday, 9 April 2015, 13:23
>  > Subject: Re: [GENERAL] unexpected (to me) sorting order
>  >
>  > On Wed, Apr 8, 2015 at 3:33 AM, Glyn Astill <glynastill@yahoo.co.uk>
>  > wrote:
>  >
>  >>
>  >>> 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
>  >>>
>  >>> 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.
>  >
>  > utf8 is an encoding method, not a collation. The collation is en_US,
>  > encoded in utf8. You can use C collation with utf8 encoding just fine.
>  > So just replace UTF8 with en_US in your sentence and you've got it
>  > right.
>  >
>
> Yes, thanks for the correction there, and we're talking about the wider
> unicode collate algorithm.

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

We have so many "sorting rules" and standards.

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');



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

-- using COLLATE need that 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" ;

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

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-