Re: How to restore a SQL-ASCII encoded database to a new UTF-8 db?

From: Tommy Gildseth <tommy(dot)gildseth(at)usit(dot)uio(dot)no>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to restore a SQL-ASCII encoded database to a new UTF-8 db?
Date: 2009-05-22 07:09:32
Message-ID: 4A164FAC.9090203@usit.uio.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Postgres User wrote:
> Hi,
>
> I have a database that was created with SQL-ASCII encoding
> (unfortunately). I ran pg_restore to load the struct and data into a
> new database with UTF-8 encoding but no surprise- I'm seeing this
> error for a number of tables:
>
> pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encod
> ing "UTF8"
>
> Any idea on how I can copy the data between these databases without
> any data loss? For some reason I thought that a conversion to Unicode
> would be easy.

Provided you haven't actually any characters from different character
sets or invalid characters in the dump, you may be able to import it
just by changing the client encoding in the dump. There's probably a
line saying something like
"SET CLIENT_ENCODING=SQL-ASCII;"
If you change that to
"SET CLIENT_ENCODING=Whatever_encoding_your_data_is_in;"

You may be able to import it. IIRC, PostgreSQL doesn't do any automatic
conversion between SQL-ASCII <-> Any encoding, but if you put the
correct encoding, PostgreSQL will deal with the conversion automatically.

--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Bailey 2009-05-22 07:09:53 Passing tokens to a function
Previous Message Albe Laurenz 2009-05-22 06:47:42 Re: How to restore a SQL-ASCII encoded database to a new UTF-8 db?