Обсуждение: PostgreSQL, change encoding preserving data

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?

Best regards.

+39.347.4070897

http://www.labcsp.com 

http://www.denisgottardello.it 

GMT+1

Skype: mrdebug

This might help: https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/

Kind Regards,

Holger

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Hi,

I think, it should be possible without changing template0/template1, 
simply by using template0 and -E parameter to (command line) createdb.

Examples:

createdb -T template0 -E latin1 foo
createdb -T template0 -E sql_ascii foo2
createdb -T template0 -E EUC_CN foo3
createdb -T template0 -E utf8 foo4


np ->psql -l |grep foo
 foo        | np                  | LATIN1    | C       | C     | 
 foo2       | np                  | SQL_ASCII | C       | C     | 
 foo3       | np                  | EUC_CN    | C       | C     | 
 foo4       | np                  | UTF8      | C       | C     | 



cheers
Norbert 

--
Norbert Poellmann EDV-Beratung             email  : np@ibu.de
Severinstrasse 5                           telefon: 089 38469995  
81541 Muenchen, Germany                    telefon: 0179 2133436 


On Thu, Feb 23, 2023 at 02:18:54PM +0100, Holger Jakobs wrote:
> Am 23.02.23 um 14:00 schrieb info@denisgottardello.it:
> >
> > 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?
> >
> > Best regards.
> >
> >
> > +39.347.4070897
> >
> > http://www.labcsp.com
> >
> > http://www.denisgottardello.it
> >
> > GMT+1
> >
> > Skype: mrdebug
> >
> This might help: 
> https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/
> 
> Kind Regards,
> 
> Holger
> 
> 
> -- 
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
> 

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