How to Change collate & ctype for an existing database?

Hi

pá 13. 12. 2019 v 9:57 odesílatel Daulat Ram <Daulat.Ram@exponential.com>
napsal:

Hi,

How we can change the Collate & Ctype from “c” to “C.UTF-8” on
existing production database.

SELECT datcollate FROM pg_database WHERE datname='wwkidbt';

datcollate

------------

C

postgres=# select version();

version

----------------------------------------------------------------------------------------------------------

PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit

There is not any official setup how to doit other than just pg_dump and
load.

Please advice about the below method:

update pg_database set datcollate='C.UTF-8', datctype='C.UTF-8' where
datname='wwkidbt';

Is there any impact on data/corruption if we do via update command ?

sure - almost all your indexes will be broken. Direct update of system
tables is usually really bad idea.

Regards

Pavel