Re: Changing collate & ctype for an existing database

rihad <rihad(at)mail(dot)ru> writes:
> On 07/10/2017 08:42 PM, Tom Lane wrote:
>> 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.

> 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'd be safe to do it on template0, and also on template1 as long as that
has only the original contents ...

> It's empty, only hosting a few extensions.

... which that isn't. I'd suggest checking for indexes that might need
to be rebuilt with this query borrowed from the regression tests:

SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll
FROM (SELECT indexrelid, indrelid,
unnest(indclass) as iclass, unnest(indcollation) as icoll
FROM pg_index) ss
WHERE icoll != 0 AND iclass !=
(SELECT oid FROM pg_opclass
WHERE opcname = 'text_pattern_ops' AND opcmethod =
(SELECT oid FROM pg_am WHERE amname = 'btree'));

> 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.

Read the HINT ...

regards, tom lane