Table of Contents Previous Next


3 Database Administration : 3.6 Unicode Collation Algorithm : 3.6.3 Creating an ICU Collation

When creating a new database cluster with the initdb command, the --icu-short-form option can be specified to define the ICU collation to be used by default by all databases in the cluster.
When creating a new database with the CREATE DATABASE command, the ICU_SHORT_FORM parameter can be specified to define the ICU collation to be used by default in that database.
In an existing database, the CREATE COLLATION command can be used with the ICU_SHORT_FORM parameter to define an ICU collation to be used under specific circumstances such as when assigned with the COLLATE clause onto selected columns of certain tables or when appended with the COLLATE clause onto an expression such as ORDER BY expr COLLATE "collation_name".
Use the ICU_SHORT_FORM parameter with the CREATE COLLATION command to create an ICU collation:
CREATE COLLATION collation_name (
[ LOCALE = locale, ]
[ LC_COLLATE = lc_collate, ]
[ LC_CTYPE = lc_ctype, ]
[ ICU_SHORT_FORM = icu_short_form ]
To be able to create a collation, you must have CREATE privilege on the destination schema where the collation is to reside.
For information about the general usage of the CREATE COLLATION command, please refer to the PostgreSQL core documentation available at:
UTF-8 character encoding of the database is required. Any LOCALE, or LC_COLLATE and LC_CTYPE settings that are accepted with UTF-8 encoding can be used.
The locale to be used. Short cut for setting LC_COLLATE and LC_TYPE. If LOCALE is specified, then LC_COLLATE and LC_TYPE must be omitted.
The collation to be used. If LC_CTYPE is specified, then LC_COLLATE must also be specified and LOCALE must be omitted.
The character classification to be used. If LC_COLLATE is specified, then LC_CTYPE must also be specified and LOCALE must be omitted.
CREATE DATABASE database_name
[ TEMPLATE [=] template ]
[ LC_COLLATE [=] lc_collate ]
[ TABLESPACE [=] tablespace_name ]
[ ICU_SHORT_FORM [=] icu_short_form ]];
For complete information about the general usage, syntax, and parameters of the CREATE DATABASE command, please refer to the PostgreSQL core documentation available at:
When using the CREATE DATABASE command to create a database using an ICU collation, the TEMPLATE template0 clause must be specified and the database encoding must be UTF-8.
The following is an example of creating a database using the LROOT ICU short form collation, but sorts an uppercase form of a letter before its lowercase counterpart (CU) and treats variable characters as non-ignorable (AN).
The following psql command shows the newly created database.
3.6.3.3 initdb
For complete information about the general usage, syntax, and parameters of the initdb command, please refer to the PostgreSQL core documentation available at:

3 Database Administration : 3.6 Unicode Collation Algorithm : 3.6.3 Creating an ICU Collation

Table of Contents Previous Next