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

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