Unicode has evolved from version 1.0 with 7,161 characters released in 1991 to version 9.0 with 128,172 characters released in June 2016. My questions are - which version of Unicode is supported by PostgreSQL 9.6.1? - what does "supported" exactly mean? simply store it? comparison? sorting? substring? etc.
Below is a test I did which reveals some unexpected behaviors. My test database 'gsdb' is using UTF8 encoding, confirmed by select datname, datcollate, datctype, pg_encoding_to_char(encoding) from pg_database where datname = 'gsdb'; which returned UTF8. Here is a simple test table: create table unicode (id int, string varchar(100)); Then I insert some unicode characters by referencing their code points in hexadecimal: /* characters from BMP, 0000 - FFFF */ insert into unicode(id, string) values(1, U&'\0041'); -- 'A' insert into unicode(id, string) values(2, U&'\00C4'); -- 'A' with umlaut, German insert into unicode(id, string) values(3, U&'\03B1'); -- Greek letter alpha insert into unicode(id, string) values(4, U&'\6211'); -- a Chinese character, https://unicodelookup.com/#0x6211/1 insert into unicode(id, string) values(5, U&'\6211\4EEC'); -- a string of two Chinese characters insert into unicode(id, string) values(6, U&'\30CF'); -- a Japanese character insert into unicode(id, string) values(7, U&'\306F'); -- a Japanese character insert into unicode(id, string) values(8, U&'\2B41'); -- https://unicodelookup.com/#0x2b41/1 insert into unicode(id, string) values(9, U&'\2B44'); -- https://unicodelookup.com/#0x2b44/1 insert into unicode(id, string) values(10, U&'\2B50'); -- https://unicodelookup.com/#0x2b50/1 /* Below are unicode characters with code points beyond FFFF, aka planes 1 - F */ insert into unicode(id, string) values(100, U&'\1F478'); -- a mojo character, https://unicodelookup.com/#0x1f478/1 insert into unicode(id, string) values(101, U&'\1F479'); -- another mojo insert into unicode(id, string) values(102, U&'\1D11F'); -- musical symbol g clef ottava alta insert into unicode(id, string) values(103, U&'\26000'); -- a very infrequently used Chinese character insert into unicode(id, string) values(104, U&'\26001'); -- another very infrequently used Chinese character insert into unicode(id, string) values(105, U&'\26000\26001'); -- a string with 2 Chinese characters in the plane 2 The SELECT below shows what PostgreSQL has recorded: select id, string, char_length(string), octet_length(string), ascii(string), substring(string, 1, 1) as firstChar, ascii(substring(string, 1, 1)) as unicodeInt from unicode order by string; Here are the results: [image: Inline image 1] Observations - BMP characters (id <= 10) - they are stored and fetched correctly. - their lengths in char are correct, although some of them take 3 bytes (id = 4, 6, 7) - *But their sorting order seems to be undefined. Can anyone comment the sorting rules?* - Non-BMP characters (id >= 100) - they take 2 - 4 bytes. - Their lengths in character are not correct - they are not retrieved correctly, judged by the their fetched ascii value (column 5 in the table above) - substring is not correct Specifically, the lack of support for emojo characters 0x1F478, 0x1F479 is causing a problem in my application. My conclusion: - PostgreSQL 9.6.1 only supports a subset of unicode characters in BMP. Is there any documents defining which subset is fully supported? Are any configuration I can change so that more unicode characters are supported? Thanks James
