How add more items into collation menu in pgAdmin 4?

Question

I want to create a new database in pgAdmin (PostgreSQL 16 on Widnows 10) but i can only chose from 3 items for database Collation.

If i list available collations by

select *
from pg_collation pgc

, there is 2245 records.

Collation menu in pgAdmin

I was looking for solution but it seams that nobody else had this problem. Is there any possiblity to edit items in Collation combobox?

Thank you. PS

[Update] I created a new database using a query widnow. I chose "cs_CZ.UTF-8" for COLLATE and CTYPE. The database was created but there are no values for collation and character type in the definition tab .

Is this normal? I should maybe read some basic informacions about the PostgreSQL database because for now i am a litle bit dissapointed. I used the Firebird database for my previous projects and there wasn't such obstacles.

Maybe Postgre 16 is too "young" and it will better to use the version 15? Or the pgAdmin is not the best tool and i should try the DBeaver? What do you think?

Answer 1

You are using an older version of PGAdmin from before PostgreSQL 15, so it doesn't expose all CREATE DATABASE parameters that would let you select a valid set of parameters required to use those other collations. You probably need to override locale_provider

locale_provider Specifies the provider to use for the default collation in this database. Possible values are icu (if the server was built with ICU support) or libc. By default, the provider is the same as that of the template. See Section 24.1.4 for details.

and icu_locale:

icu_locale Specifies the ICU locale (see Section 24.2.2.3.2) for the database default collation order and character classification, overriding the setting locale. The locale provider must be ICU. The default is the setting of locale if specified; otherwise the same setting as the template database.

As a workaround, you can connect to any database on this cluster, open a regular SQL window and run the command:

create database collation_test2 
   template template0
   encoding 'UTF8'
   locale_provider icu 
   icu_locale 'cs-CZ-x-icu';

Or update to PGAdmin 7.7 or above, where those parameters are exposed through the database dialog.