Thank you, friend, I use --hex-blob : mysqldump -v -nt --complete-insert=TRUE --compatible=postgresql --default-character-set=utf8 --skip-add-locks --compact --no-create-info --skip-quote-names --hex-blob -uroot -p test videorecresult >dbdata.sql to dump mysql data. And replace blob data "0x...." into "E'\\xx....'" to load data into postgresql.
On Fri, Jul 4, 2014 at 3:27 PM, Albe Laurenz <laurenz.a...@wien.gv.at> wrote: > sunpeng wrote: > >>> load data to postgresql in cmd(encoding is GBK) is WIN8: > >>> > >>> psql -h localhost -d test -U postgres < dbdata.sql > >>> > >>> I got the error: > >>> ERROR: invalid byte sequence for encoding "UTF8": 0xff > > >> If the encoding is GBK then you will get errors (or incorrect > >> characters) if it is read as UTF8. Try setting the environment > >> variable PGCLIENTENCODING. > >> > >> http://www.postgresql.org/docs/9.1/static/app-psql.html > > > I‘v changed cmd (in win8) to encoding utf8 through chcp 65001, but error > still occurs. > > And i use the following cmd to dump mysql data: > > mysql> select Picture from personpicture where id = > 'F2931306D1EE44ca82394CD3BC2404D4' into outfile > > "d:\\1.txt" ; > > I got the ansi file, and use Ultraedit to see first 16 bytes: > > FF D8 FF E0 5C 30 10 4A 46 49 46 5C 30 01 01 5C > > > > It's different from mysql workbench to see: > > FF D8 FF E0 00 10 4a 46 49 46 00 01 01 00 00 01 > > Changing the terminal code page won't do anything, it's probably the data > that are in a different encoding. > > I don't know enough about MySQL to know which encoding it uses when > dumping data, > but the man page of "mysqldump" tells me: > > --set-charset > Add SET NAMES default_character_set to the output. This option is > enabled by default. > > So is there a SET NAMES command in the dump? If yes, what is the argument? > > You will have to tell PostgreSQL the encoding of the data. > As Kevin pointed out, you can do that by setting the environment variable > PGCLIENT ENCODING to the correct value. Then PostgreSQL will convert the > data automatically. > > Yours, > Laurenz Albe >
