Is it possible to change collation for an existing database with PostgreSQL?

Such as change from C to utf8?

I tried this but seems not allowed.

postgres=# ALTER DATABASE mydb SET "Collate" To 'en_US.UTF-8';
ERROR:  unrecognized configuration parameter "Collate"

No, that is not possible.

The reason is that there may be data structures in the database that depend on the database's collation: indexes over string data types. If you'd be able to change the collation on the fly, all these indexes would implicitly become corrupted.

You might say that is OK, let's just rebuild all these indexes. Well, if you are ready to do that, there is a way: pg_dump the database, create a new database with the desired collation and restore the dump into the new database. Sure, that will take longer than just rebuilding the string indexes, but the expense is in the same order of magnitude, and this is a safe way.

You may be tempted to meddle with the pg_database system catalog manually, but I can only discourage that: as the saying is, if you do that and things break, you get to keep both pieces.

StackExchange Database Administrators Q#275533, answer score: 5