Specify ICU collations as `ENCODING`, `LC_COLLATE`, and `LC_CTYPE` in Postgres 10

Question

Postgres 10 gains the ability to use International Components for Unicode (ICU) collations rather than depending on host OS implementations. See More robust collations with ICU support in PostgreSQL 10 by Peter Eisentraut.

So how exactly does one specify an ICU collation when creating the database?

I did read a tip in the CREATE DATABASE to use template0 rather than template1 if template1 might have been created using encoding and collation other than what you want for your new database. And when using template0, you need to enable data connections to override the default.

And I read in Postgres 10 doc page for Collation Support that:

und-x-icu (for “undefined”)

ICU “root” collation. Use this to get a reasonable language-agnostic sort order.

So it seems und-x-icu might be a good value to use when establishing a new database that will include multi-lingual text. When a certain language is known to focus on a particular language, the SELECT command can specify another collation such as German de-x-icu. Otherwise, fallback to the default of und-x-icu.

So I tried the following SQL to create a database in Postgres 10 Beta 2.

CREATE DATABASE timepiece_
TEMPLATE 'template0'
ALLOW_CONNECTIONS TRUE
CONNECTION LIMIT -1
ENCODING 'UTF8'
LC_COLLATE 'und-x-icu'
LC_CTYPE 'und-x-icu'
;

Fails with an error:

[42809] ERROR: invalid locale name: "und-x-icu"

So I stuck on an appended .utf8 as seen in the doc for the old-fashioned collation syntax.

CREATE DATABASE timepiece_
TEMPLATE 'template0'
ALLOW_CONNECTIONS TRUE
CONNECTION LIMIT -1
ENCODING 'UTF8'
LC_COLLATE 'und-x-icu.utf8'
LC_CTYPE 'und-x-icu.utf8'
;

Fails just the same:

[42809] ERROR: invalid locale name: "und-x-icu.utf8"

Was my Postgres cluster built with ICU libraries? Here is the result of calling pg_config. The last line is 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'. I guess that means my build includes the ICU libraries but I am not sure.

/Library/PostgreSQL/10Beta2/bin/pg_config --configure

'--with-icu' '--prefix=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx' '--with-ldap' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--with-bonjour' '--with-pam' '--enable-thread-safety' '--with-libxml' '--with-uuid=e2fs' '--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include:/opt/local/Current/include/security' '--docdir=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx/doc/postgresql' '--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 'CFLAGS=-isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.8.sdk -mmacosx-version-min=10.8 -arch i386 -arch x86_64 -O2' 'LDFLAGS=-L/opt/local/Current/lib' 'ICU_CFLAGS=-I/opt/local/Current/include' 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'

➠ How does one specify the ENCODING, LC_COLLATE, and LC_TYPE to use the new ICU collations?

➠ What ICU collation names you would suggest for English-oriented databases? For Western European multi-lingual databases?

➠ What are the possible ICU collation names?

Answer 1

First let's note that at the time of this answer, PostgreSQL 10 in still in beta stage. Some issues about the ICU integration and how it's documented are still under discussion, and there might be changes before a GA release.

➠ How does one specify the ENCODING, LC_COLLATE, and LC_TYPE to use the new ICU collations?

It's not possible currently (and probably not documented well enough, or not clear enough in the error message: when it says invalid locale name, it means from the set of locales provided by libc).
This is discussed in this thread on the developers mailing list: Can ICU be used for a database's default sort order?

➠ What ICU collation names you would suggest for English-oriented databases? For Western European multi-lingual databases?

It mostly matters if you have specific sort requirements. In this other thread of the mailing-list:
What users can do with custom ICU collations in Postgres 10
some concrete examples are given of ICU collations behaving specially compared to what libc can do.

When not having specific requirements, I think most applications won't care whether they use an ICU collation or a libc en_US collation, except for portability. One of the big points of ICU is to get exactly the same ordering across all operating systems for a given locale, which is really not the case of libc. I guess that the pre-created en-x-icu would be fine for general English, and for West-european languages, I'm not sure why any collation would be better than und-x-icu.

As a side-note, it's not a good idea to use any other collation than C when you don't really care about the sort rules, because C as a collation will outperform any language-aware collation. And C is portable across operating systems.

➠ What are the possible ICU collation names?

Although initdb populates pg_collation with a list of pre-defined collations, a complete list would be subject to combinatorial explosion, as ICU collation names are formed by assembling tags, as listed here: http://unicode.org/reports/tr35/tr35-collation.html#Setting_Options

Currently the doc does not mention explicitly that you can do:

CREATE COLLATION mycoll (locale='my-set-of-tags', provider='icu');

but I think work is happening right now to improve that in the doc.

Another issue that might be worth pointing out is that there are multiple ICU major versions out there, and the list of pre-defined collations that initdb instantiates differs significantly between these versions, especially ICU pre-53 versus post-53. This has been put in evidence in another discussion: Crash report for some ICU-52 (debian8) COLLATE and work_mem values

EDIT: in the next beta or RC, the list of ICU collations created by initdb will change thoroughly , as mentioned in this discussion. The list will made by iterating on ICU locales rather than collations, and as a result the question "What are the possible ICU collation names?" will have to be reconsidered with the new values.