Re: How to Change collate & ctype for an existing database?

Hi

pá 13. 12. 2019 v 9:57 odesílatel Daulat Ram <Daulat(dot)Ram(at)exponential(dot)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

> Thanks,
>
> Daulat
>
>
>
>
>
>
>
>
>