postgresqlcollation

Enable national collation on PostgreSQL database installation

I want to create database on PostgreSQL installation on Alma linux (RedHat derivate) with this command:

CREATE DATABASE "ASP-ACDCliste" WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'Czech_Czechia.1250';

and I get this message back

psql:demo.out:97: ERROR:  invalid LC_COLLATE locale name: "Czech_Czechia.1250"
DOPORUČENÍ:  If the locale name is specific to ICU, use ICU_LOCALE.

What should I do with PostgreSQL or linux installation so that enable there Czech collation and successfully create mentioned table ?

I tried also re-initialize database on server where postgresql is installed and run:

export LANG="cs_CZ.utf8"
/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data

but without success.

Solution

  • Either use an existing C library collation:

    CREATE DATABASE "ASP-ACDCliste" 
   TEMPLATE = template0
   ENCODING = UTF8
   LOCALE_PROVIDER = libc
   LOCALE = "cs_CZ.utf8";

    or use an ICU locale:

    CREATE DATABASE "ASP-ACDCliste" 
   TEMPLATE = template0
   ENCODING = 'UTF8'
   LOCALE_PROVIDER = icu
   ICU_LOCALE = "cs-CZ"
   LOCALE = "cs_CZ.utf8";