Message-ID: <eaed12d75cc4a053c0e266901787b5dda6f69dbc.camel@cybertec.at>
Subject: Re: PostgreSQL, change encoding preserving data
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: info@denisgottardello.it, pgsql-admin@lists.postgresql.org
Date: Fri, 24 Feb 2023 14:00:12 +0100
In-Reply-To: <RQJATA$1E1680D647802A67AD54279BC45B25E6@denisgottardello.it>
References: <RQJATA$1E1680D647802A67AD54279BC45B25E6@denisgottardello.it>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/eaed12d75cc4a053c0e266901787b5dda6f69dbc.camel%40cybertec.at>
Precedence: bulk

On Thu, 2023-02-23 at 14:00 +0100, info@denisgottardello.it wrote:
> Hi, my database is setup with encoding=3D win1252. In order to go to utf8=
 I run the followed query:
>=20
> update pg_database set encoding=3D pg_char_to_encoding('utf8') where datn=
ame=3D 'Handyman'
>=20
> The problem is that, the database has already got data with special chara=
cters such
> as "=C3=A0=C3=B2=C3=A8=C3=A9". 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