Thread: Change the character encoding of a database

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?
--
Ivo Rossacher

On Jun 28, 2004, at 11:38 PM, Ivo Rossacher wrote:

>
> 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

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

Am Dienstag, 29. Juni 2004 16:29 schrieb James Cooper:
> 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/
>
> Ivo Rossacher wrote:
> > 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?

--
Ivo Rossacher