Re: postgresql encoding

[Miguel Enrique Cobá Martínez <m.coba.m@gmail.com>]

Tony Heal wrote:
> I have just upgraded my distro from woody to sarge and postgresql's encoding
> got messed up. Is there a way to change the encoding of a database without
> dumping and recreating it? I also need to change the default decoding of
> postgresql. It is set to SQL_ASCII and I want to change it to UNICODE
>
> Tony
I think that you have to dump your db and then use iconv to do the 
conversion for you. Then you must create a new db with unicode as locale 
and reload your db.

$pg_dump -U user databasename > db_dump.sql

$iconv -f ASCII -t utf8 db_dump.sql > db_dump_utf8.sql

$(optional) dropdb -Uuser databasename

$createdb -Eunicode -Uuser newdbname

$psql -Uuser newdbname < db_dump_utf8.sql


Probably you should change your pg_hba.conf to trust for all databases 
and all user, to avoid enter passwords during the reload. At the end you 
can revert this to the original permission

miguel