Re: [GENERAL] Changing collate & ctype for an existing database

On 07/10/2017 08:42 PM, Tom Lane wrote:
> rihad <rihad@mail.ru> writes:
>> Hi there. We have a working database that was unfortunately created by
>> initdb with default ("C") collation & ctype. All other locale specific
>> settings have the value en_US.UTF-8 in postgresql.conf. The database
>> itself is multilingual and all its data is stored in UTF-8. Sorting
>> doesn't work correctly, though. To fix that, can I just do this:
>> update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
>> where datname='mydb';
> No, your indexes on text/char/varchar columns will be corrupted
> (because their sort order will now be wrong).  If you can reindex
> them before doing anything more with the database, you'd be ok
> ... I think.  Testing on a scratch copy of the database would be
> a good idea, if this is valuable data.
>
>             regards, tom lane
>
Thank you, Tom. But can I still do it for the template1 database?

update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
where datname='template1';

It's empty, only hosting a few extensions. Now I can't even create a
database having a different collation:

$ createdb -O myuser --locale='en_US.UTF-8' mydb
createdb: database creation failed: ERROR:  new collation (en_US.UTF-8)
is incompatible with the collation of the template database (C)
HINT:  Use the same collation as in the template database, or use
template0 as template.