Re: unexpected (to me) sorting order - Mailing list pgsql-general
From | Jukka Inkeri |
---|---|
Subject | Re: unexpected (to me) sorting order |
Date | |
Msg-id | 5527C3EE.9050909@awot.fi Whole thread Raw |
In response to | Re: unexpected (to me) sorting order (Scott Marlowe <scott.marlowe@gmail.com>) |
List | pgsql-general |
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-
pgsql-general by date: