Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am migrating some data from a MySql database (v5.1.44/MyISAM/collation=latin1_swedish_ci) to a PostgreSQL (v9.0.4/the one included in OSX Lion).

I'm using

$ mysqldump --compatible=postgresql > tmp.sql # output create/insert statements
$ psql --command='\i tmp.sql' # import to postgresql

However the import fails with the error ERROR: invalid byte sequence for encoding "UTF8": 0xe97261 (This is in reference to accented letters).

The issue, I think, being that the exported file is not using utf-8.

The file that is exported shows the following file information

$ file tmp.sql
tmp.sql: Non-ISO extended-ASCII text, with very long lines

What's the easiest, scriptable way to get this file prepared in utf-8 for psql?

This does not work:

$ iconv -f ASCII -t UTF-8 tmp.sql > out.sql
iconv: tmp.sql:18:59270: cannot convert

I've found that opening the file in vim and issuing :set fenc=utf-8 does make the import run smoothly, but this has to be automated so I need to cut out this manual step.

share|improve this question
Have you tried: mysqldump --default-character-set=charset_name? – Carlos Feb 29 '12 at 2:05
@mastashake57, I had not tried that and adding --default-character-set=utf8 does indeed solve my issue. If you write that up as an answer I will accept it. Cheers. – davur Feb 29 '12 at 2:37
Thanks a bunch! – Carlos Feb 29 '12 at 2:59
up vote 1 down vote accepted

Try the following:

mysqldump --default-character-set=charset_name
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .