|laziness, impatience, and hubris
Re: dealing with encoding while converting data from MySQL to Postgresby grantm (Parson)
|on Dec 12, 2011 at 01:14 UTC
'invalid byte sequence for encoding "UTF8": 0x93 at...'
That message suggests that (at least this row of) your data is not encoded in UTF-8 or Latin-1 but actually CP1252 - Microsoft's embraced and extended Latin-1 which replaces some of the control characters with smart quotes etc.
If your data has a mixture of UTF-8, ASCII, Latin-1 and CP1252 then fixing that is exactly the problem that Encoding::FixLatin was designed to fix.
The Encoding::FixLatin distribution includes a command-line tool called fix_latin which you can pipe your dump file through.
In cases such as yours where you're not able to restore a dump, then you can use the Encoding::FixLatin module in your script that does the inserts and call the fix_latin() function to convert each column value to UTF8.
Note: when you connect to the Postgres database you'll want to set the pg_enable_utf8 flag in the connection attributes so that you get UTF8 characters back from a select.