pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL, change encoding preserving data
4+ messages / 4 participants
[nested] [flat]
* PostgreSQL, change encoding preserving data
@ 2023-02-23 13:00 info@denisgottardello.it
0 siblings, 2 replies; 4+ messages in thread
From: info@denisgottardello.it @ 2023-02-23 13:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL, change encoding preserving data
@ 2023-02-23 13:18 Holger Jakobs <holger@jakobs.com>
parent: info@denisgottardello.it
1 sibling, 1 reply; 4+ messages in thread
From: Holger Jakobs @ 2023-02-23 13:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../2af47a85-0004-fcb3-80c3-467e0468d07f@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL, change encoding preserving data
@ 2023-02-23 13:52 Norbert Poellmann <np@ibu.de>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Norbert Poellmann @ 2023-02-23 13:52 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL, change encoding preserving data
@ 2023-02-24 13:00 Laurenz Albe <laurenz.albe@cybertec.at>
parent: info@denisgottardello.it
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2023-02-24 13:00 UTC (permalink / raw)
To: info@denisgottardello.it; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-02-24 13:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-23 13:00 PostgreSQL, change encoding preserving data info@denisgottardello.it
2023-02-23 13:18 ` Holger Jakobs <holger@jakobs.com>
2023-02-23 13:52 ` Norbert Poellmann <np@ibu.de>
2023-02-24 13:00 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox