Change the character encoding of a database

[Ivo Rossacher]

Hi All,

I do have a database which was created with the encoding SQL_ASCII and used
with German, French and Italien strings. Until recently there was only a
Linux client, an all worked fine (Suse 8.1). Now there is a new Windows
client which does have a different idea of the encoding. I did some tests and
found out that the encoding UNICODE does work properly for both clients.
The question now is how to change the encoding of database from SQL_ASCII to
UNICODE?

I did try to dump the database by pgdump database > dump.dmp, droped the
database, recreate it with the encoding UNICODE and restore the dump by psql
database < dump.dmp which worked after some small changes (change the client
encoding to UNICODE) in the dump.dmp. Without the changes in the dump.dmp
there was a error message about invalide codes and no import at all. With the
changes there are still some encoding mistakes (wrong signes in the strings)
but the import basically works. The server actually runs on the same machine
as the client (Suse 8.1). I think there is a problem with the import because
the linux system is not set to use UNICODE. Is there an way to get the data
converted correctly?

[Jose' Cruanyes]

Post by Ivo Rossacher
I did try to dump the database by pgdump database > dump.dmp, droped the
database, recreate it with the encoding UNICODE and restore the dump by psql
database < dump.dmp which worked after some small changes (change the client
encoding to UNICODE) in the dump.dmp. Without the changes in the dump.dmp
there was a error message about invalide codes and no import at all. With the
changes there are still some encoding mistakes (wrong signes in the strings)
but the import basically works.

I've solved this changing the encoding of the dump.dmp file from ASCII
to UTF8

Pax et Bonum

# dott. Jose' Cruanyes Aguilar - C.E. Soft srl
# Pzza. Firenze,4 MILANO - XX Settembre 10, CREMONA
# 02,33603122 0372,460602


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Ivo Rossacher]

This did finaly work for me. I have to add that to get this working it is
required to have a UTF-8 aware system on which the psql runs during import.
Since the database server is not a UTF-8 system, my tries with this approche
on the server only did fail.

Thanks a lot.

Best regards
Ivo Rossacher

Ah yes, if there points for FAQ this would top the bill. My approach was
to dump the database, create a new one as UNICODE and then used a tool
called "recode" to convert the dumped database to UNICODE and the
imported into Postgre.
http://recode.progiciels-bpi.ca/

Post by Ivo Rossacher
Hi All,
I do have a database which was created with the encoding SQL_ASCII and
used with German, French and Italien strings. Until recently there was
only a Linux client, an all worked fine (Suse 8.1). Now there is a new
Windows client which does have a different idea of the encoding. I did
some tests and found out that the encoding UNICODE does work properly for
both clients. The question now is how to change the encoding of database
from SQL_ASCII to UNICODE?
I did try to dump the database by pgdump database > dump.dmp, droped the
database, recreate it with the encoding UNICODE and restore the dump by
psql database < dump.dmp which worked after some small changes (change
the client encoding to UNICODE) in the dump.dmp. Without the changes in
the dump.dmp there was a error message about invalide codes and no import
at all. With the changes there are still some encoding mistakes (wrong
signes in the strings) but the import basically works. The server
actually runs on the same machine as the client (Suse 8.1). I think there
is a problem with the import because the linux system is not set to use
UNICODE. Is there an way to get the data converted correctly?