Changing encoding of a database

Hi Tomi.

Thanks for your answer, I was not aware of such a tool.

The next question at this point is (of course): what is the problem if
I have blob? Should I recode them as well?

Regards
Marco

On 6/20/06, Tomi NA <hefest@gmail.com> wrote:

On 6/19/06, Marco Bizzarri <marco.bizzarri@gmail.com> wrote:

I all.

We've PostgreSQL database, with SQL_ASCII or LATIN1 encoding. We would
like to migrate them to UNICODE. Is there some contributed/available
script, or this is something we should do at hand?

Regards
Marco

If you don't have blobs in your database, dump it to insert
statements, use the recode tool to recode your data, create a new
database based on UTF8 and load the data.

t.n.a.

--
Marco Bizzarri
http://notenotturne.blogspot.com/

We've PostgreSQL database, with SQL_ASCII or LATIN1 encoding. We would
like to migrate them to UNICODE. Is there some contributed/available
script, or this is something we should do at hand?

I had a similar problem migrating from 7.4 to 8.1 and wanting to
go from sql_ascii to utf8. I did the following:

pg_dump -p 5433 --encoding ISO_8859_7 -t cas tj |psql tj

where the dump connected to 7.4 (port 5433) and interpreted the
cas data using ISO_8859_7. psql connected to 8.1
I had to experiment to find that ISO_8859_7 was the "proper"
encoding - i had some greek (math and chemistry) letters which
were accomodated by sql_ascii, but not quite "properly".
The output from pg_dump above properly converts to utf8
which 8.1 (i set the default enccoding utf8) accepts without complaint.

See http://www.postgresql.org/docs/8.1/static/multibyte.html
for all the other encodings.

I don't think the above will convert a table in place, but could be
used to create a copy with changed encoding.
Hope this helps.

TJ