Setting collate and ctype during database creation

[Zsirafovics]

I've prepared a modification to setting collate and ctype during database creation.
Why is it important and necessary?

PostgreSQL uses en_GB.UTF-8 by default (on openSUSE), but this doesn't guarantee proper operation in the localized (e.g. CH, HU, PL, SK, etc...) environment. What do I mean by this?
If the locale does not match the users' language:

• sorting will be incorrect
• searching will not work properly
• LIKE operations will behave incorrectly
• LOWER/UPPER will work incorrectly
• regexes will be considered incorrect for certain accented characters
  In practice, this causes very annoying errors in an ERP system that uses accented data.

The COLLATE and CTYPE values of a PostgreSQL database cannot be changed after creation. Therefore, it is important to set these values correctly when creating the database.
The modification I made, allows you to select the locale from a drop-down list when configuring database settings in the admin interface. The list contains the languages found in the locale directory that have UTF encoding.

If anybody has question or idea don't hesitate to share with me.

[Tekki]

Good explanation and good explication why this is necessary.

The technical description is incomplete: You have to query Postgres for the locales it knows about, not parse locale.gen. The query you need is

SELECT collname FROM pg_collation WHERE collname LIKE '%utf8';

Postgres gets aware of the available locales when it is first initialized (Postgres itself, not the database). This means the locales you're going to use have to be present at the very beginning. And ideally template0 and template1 already have your preferred collation.
Take a look at the order of the installation in the Ansible role.

[Note to myself: The Ansible role should be able to install multiple locales.]

[Zsirafovics]

And ideally template0 and template1 already have your preferred collation.

Yes. Ideally, when You should create each database for your own locale. However, even then, it is worth leaving en_GB-UTF8 or en_US-UTF8 collate for template0 and template1.
When You'd like to use different locales individual databases, the base must always be en_US(or GB)-UTF8.
Incidentally, I always set the operating system language for the server to en_GB, even if the applications running on it are in Hungarian.

[Tekki]

the base must always be en_US(or GB)-UTF8

We can look more closely at this when we start to use tsvectors and trigram indices.