postgresql 12.3 convert existing DB to use case-insensitive collation?

Question

postgresql 12.3 in a container with Alpine Linux 3.10

I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping it and recreating from scratch. Also, instead of setting this for the whole db, can I add this collation to a field in an existing table, or to a table?

Answer 1

First, a warning: there used to be problems with collations on Alpine Linux, so consider using something else.

The only way to change a a database to use a case insensitive collation throughout is to create such a collation and then use

ALTER TABLE ... ALTER ... TYPE text COLLATE your_coll;

for all string columns in the database.

ICU collations cannot (yet) be used as the default collation for a database.