FWD » Core Development » Database

We currently match legacy database collation for non-UTF-8 databases (at least for Linux-based PostgreSQL) by installing a custom locale which defines a collation compatible with the original database. We define various locales to match particular combinations of character encoding and locale.

This is manageable when the character set is limited to 256 characters, but we need a more manageable strategy for Unicode databases. ICU (https://icu.unicode.org/) seems a likely solution, but we need to determine how best to use it across the various databases and OS platforms we need to support.

From #9378-8 :

This looks like is just how en_US.UTF-8 collation works, and is not a postgresql problem, but a collation problem - see an online demo here: https://onecompiler.com/postgresql/42z7g7r6d

And the collation on linux looks to show the same results, check this on a machine with the en_US.UTF-8 locale:

LC_ALL=en_US.UTF8 ; if [[ "a" < "<b" ]] ; then echo 'yes' ; else echo 'no' ; fi

it shows 'yes', same as the sorting in postgresql.
If you run it via:

LC_ALL=C.UTF8 ; if [[ "a" < "<b" ]] ; then echo 'yes' ; else echo 'no' ; fi

it will show 'no'.

Now, postgresql has the ICU (instead of libc) string comparison - see https://www.cybertec-postgresql.com/en/icu-collations-against-postgresql-data-corruption/#:~:text=PostgreSQL%20does%20not%20define%20its,provided%20by%20the%20ICU%20library. and also this 10-year old article about issues with the libc sorting https://github.com/PostgresApp/PostgresApp/issues/216

And ICU looks to sort properly:

fwd=# select * from tt1 order by f1 collate "en-US-x-icu";
 f1  
-----
 <b>
 a
 c
(3 rows)

And also, if we have the custom UTF-8 collation, and we need to fix the database's collation for existing production installs: will it be enough to do a update pg_database set datcollate=... or will the cluster require to be recreated, dump the database and re-import it? Or maybe just re-indexed on the new collation?

• an answer to this question is that update pg_database set datcollate= will not work, will break the database completely.

So, for production (or even staging/dev) installs, to move to ICU existing databases we will need:

• example command how to create the cluster
• a command/script to back and restore the database to the new cluster. Keep in mind that the databases can be very large, some in the 100's of GB, so dumping to sql will create a very large file. There should be a way to pipe the output from the backup command to the restore command's input.

This shows that UTF-8 collation does not sort records properly:

create table tt1 (f1 text );
create index ix1_tt1 on tt1 (f1);
insert into tt1 values ('c');
insert into tt1 values ('a');
insert into tt1 values ('<b>');
select * from tt1 order by f1;  -- wrong order, 'a <b> c'
select * from tt1 order by f1 collate "nl-NL-x-icu";  -- correct order, '<b> a c'
drop table tt1;