PostgreSQL 16 is running on Red Hat Enterprise Linux 9, and the following error occurred while trying to create a database with collation Canadian-en_CA.UTF-8:
ERROR: collation "Canadian_C_AI" does not exist
However, the local virtual machine only has the Canadian-en_CA.UTF-8 locale installed.
To resolve this issue, you can create the required collation as follows:
- Connect to the PostgreSQL server as a superuser:
\c postgres
- Create the Canadian_C_AI collation:
CREATE COLLATION canadian_c_ai (
provider = icu,
locale = "en_CA"
);
- Recreate the database with the specified collation:
CREATE DATABASE mydatabase WITH OWNER = myuser ENCODING = 'UTF8' COLLATE = 'canadian_c_ai';
Replace mydatabase and myuser with your desired database name and user, respectively.
Reference(s):
- PostgreSQL Documentation - Collations
- Red Hat - Setting the Default Locale
- ICU User's Guide - Collation