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

On 07/10/2017 11:07 PM, Tom Lane wrote:
> 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'));
>
I ran the query on our production database. Zero results. Do I have the
green light to

set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'

for all our working databases? :) Or for template0 & template1 only?