Re: unexpected (to me) sorting order - Mailing list pgsql-general
From | Jukka Inkeri |
---|---|
Subject | Re: unexpected (to me) sorting order |
Date | |
Msg-id | 5527CC6E.5070408@awot.fi Whole thread Raw |
In response to | unexpected (to me) sorting order (Björn Lundin <b.f.lundin@gmail.com>) |
List | pgsql-general |
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-
pgsql-general by date: