Re: PostgreSQL, change encoding preserving data

On Thu, 2023-02-23 at 14:00 +0100, info@denisgottardello.it wrote:
> Hi, my database is setup with encoding= win1252. In order to go to utf8 I run the followed query:
> 
> update pg_database set encoding= pg_char_to_encoding('utf8') where datname= 'Handyman'
> 
> The problem is that, the database has already got data with special characters such
> as "àòèé". After having executed the query to change the encoding to utf8, I can't read the special caracters.
> Is there a way to change the encoding and to migtrate the data?

Congratulations, you have broken your database.
Undo that change right away.  Don't update catalog tables.

The only way to do that is dump/restore.

Yours,
Laurenz Albe