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

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