rihad <rihad@mail.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
templatedatabase (C)
> HINT: Use the same collation as in the template database, or use template0 as template.
Read the HINT ...
regards, tom lane