Thread: Change/convert encoding?

hello,

i have a database and in it strings with accented characters in
latin-1 encoding, but the database has been mistakenly created with
unicode encoding. as you all know this makes pg_dump break.

what i'm enquiring is - is there a way to change the encoding
and preserve the data? i only know of one way to specify the
encoding, initdb -e, but afaik that will nuke all the data also.
and i can't use pg_dump, obviously...

i'm using postgresql version 6.5.18.

ps. i also get odd effects like strings with "ä" turning into
"(e6)" at times, i suppose it's also related the incorrect
encoding setting?

   -- erno

Erno Kuusela <erno@iki.fi> writes:
> i have a database and in it strings with accented characters in
> latin-1 encoding, but the database has been mistakenly created with
> unicode encoding. as you all know this makes pg_dump break.

> what i'm enquiring is - is there a way to change the encoding
> and preserve the data? i only know of one way to specify the
> encoding, initdb -e, but afaik that will nuke all the data also.

I believe you could just reach into pg_database and update the
encoding field for that DB.  Probably best to do so with a backend
connected to a different DB, like template1.

            regards, tom lane