On 07/10/2017 08:42 PM, Tom Lane wrote:
rihad <rihad@xxxxxxx> 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
