Обсуждение: Load Mysql table CSV into postgresql
Hi,
Today i need to load some mysql ( 5.1.58 ) tables dump in postgresql ( PG 9.2 ). I loaded few tables successfully but while loading one table i am facing below error :
test=# copy jobs from '/tmp/test.csv' with DELIMITER AS ',' QUOTE '"' NULL AS '\N' ESCAPE E'\\' CSV;
ERROR: invalid byte sequence for encoding "UTF8": 0xc7 0x3c
CONTEXT: COPY jobs, line 259
After some research , i think it is failing because Mysql table has character set latin1 format and PG9.2 has by deafult UTF-8 format. But don't understand how other tables got successfully loaded. Below link tells to use iconv :Today i need to load some mysql ( 5.1.58 ) tables dump in postgresql ( PG 9.2 ). I loaded few tables successfully but while loading one table i am facing below error :
test=# copy jobs from '/tmp/test.csv' with DELIMITER AS ',' QUOTE '"' NULL AS '\N' ESCAPE E'\\' CSV;
ERROR: invalid byte sequence for encoding "UTF8": 0xc7 0x3c
CONTEXT: COPY jobs, line 259
http://www.perlmonks.org/?node_id=942765 , but still the same error.
On 06/06/2013 10:33 AM, Adarsh Sharma wrote: > Hi, > > Today i need to load some mysql ( 5.1.58 ) tables dump in postgresql ( > PG 9.2 ). I loaded few tables successfully but while loading one table i > am facing below error : > > test=# copy jobs from '/tmp/test.csv' with DELIMITER AS ',' QUOTE '"' > NULL AS '\N' ESCAPE E'\\' CSV; > ERROR: invalid byte sequence for encoding "UTF8": 0xc7 0x3c > CONTEXT: COPY jobs, line 259 > > > After some research , i think it is failing because Mysql table has > character set latin1 format and PG9.2 has by deafult UTF-8 format. But > don't understand how other tables got successfully loaded. Probably because ASCII, Latin1 and UTF8 share the first 127 characters and your other tables did not have data that used characters above the first 127. Below link > tells to use iconv : > > http://www.perlmonks.org/?node_id=942765 , but still the same error. Might try test=#\encoding LATIN1 and then your copy command or use the ENCODING parameter to COPY: http://www.postgresql.org/docs/9.2/interactive/sql-copy.html > > My doubt is in this table there is one MEDIUMBLOB that i used as BYTEA > in postgresql. Is it was due to that or anyone fixed this issue earlier > , plz update. > > > Thanks -- Adrian Klaver adrian.klaver@gmail.com
Thanks Adrian , i think it works but still facing problem while loading Mysql ( MEDIUMBLOB ) column into PG ( BYTEA ) column :
test=# \encoding LATIN1;
test=# copy jobs from '/tmp/test.csv' with DELIMITER AS ',' QUOTE '"' NULL AS '\N' ESCAPE E'\\' CSV;
ERROR: invalid input syntax for type bytea
CONTEXT: COPY jobs, line 259, column wf_instance: "\0$0000000-120805203721153-oozie-oozi-W\0\0)<?xml version="1.0" encoding="UTF-8" standalone="no"?><..."
test=#
test=# \encoding LATIN1;
test=# copy jobs from '/tmp/test.csv' with DELIMITER AS ',' QUOTE '"' NULL AS '\N' ESCAPE E'\\' CSV;
ERROR: invalid input syntax for type bytea
CONTEXT: COPY jobs, line 259, column wf_instance: "\0$0000000-120805203721153-oozie-oozi-W\0\0)<?xml version="1.0" encoding="UTF-8" standalone="no"?><..."
test=#
wf_instance is single mediumblob column in the table.
On Thu, Jun 6, 2013 at 11:19 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 06/06/2013 10:33 AM, Adarsh Sharma wrote:Probably because ASCII, Latin1 and UTF8 share the first 127 characters and your other tables did not have data that used characters above the first 127. Might tryHi,
Today i need to load some mysql ( 5.1.58 ) tables dump in postgresql (
PG 9.2 ). I loaded few tables successfully but while loading one table i
am facing below error :
test=# copy jobs from '/tmp/test.csv' with DELIMITER AS ',' QUOTE '"'
NULL AS '\N' ESCAPE E'\\' CSV;
ERROR: invalid byte sequence for encoding "UTF8": 0xc7 0x3c
CONTEXT: COPY jobs, line 259
After some research , i think it is failing because Mysql table has
character set latin1 format and PG9.2 has by deafult UTF-8 format. But
don't understand how other tables got successfully loaded.
test=#\encoding LATIN1
and then your copy command
or
use the ENCODING parameter to COPY:
http://www.postgresql.org/docs/9.2/interactive/sql-copy.html--
My doubt is in this table there is one MEDIUMBLOB that i used as BYTEA
in postgresql. Is it was due to that or anyone fixed this issue earlier
, plz update.
Thanks
Adrian Klaver
adrian.klaver@gmail.com
On 06/06/2013 12:58 PM, Adarsh Sharma wrote: > Thanks Adrian , i think it works but still facing problem while loading > Mysql ( MEDIUMBLOB ) column into PG ( BYTEA ) column : > > test=# \encoding LATIN1; > test=# copy jobs from '/tmp/test.csv' with DELIMITER AS ',' QUOTE '"' > NULL AS '\N' ESCAPE E'\\' CSV; > ERROR: invalid input syntax for type bytea > CONTEXT: COPY jobs, line 259, column wf_instance: > "\0$0000000-120805203721153-oozie-oozi-W\0\0)<?xml version="1.0" > encoding="UTF-8" standalone="no"?><..." > test=# > > wf_instance is single mediumblob column in the table. I have not done that conversion. All I can do is point you at this link, courtesy of Google: http://dba.stackexchange.com/questions/4211/how-to-migrate-large-blob-table-from-mysql-to-postgresql Others on the list may have better ideas. > > Cheers -- Adrian Klaver adrian.klaver@gmail.com
Adarsh Sharma wrote on 06.06.2013 19:33: > Hi, > > Today i need to load some mysql ( 5.1.58 ) tables dump in postgresql > ( PG 9.2 ). I loaded few tables successfully but while loading one > table i am facing below error : > > After some research , i think it is failing because Mysql table has > character set latin1 format and PG9.2 has by deafult UTF-8 format. > But don't understand how other tables got successfully loaded. Below > link tells to use iconv : > To copy data from one DBMS to another you might want to have a look at my tool SQL Workbench/J The export/import feature is especially tailored for things like that including BLOB data that can be handled by plain text exports and imports. http://www.sql-workbench.net/manual/command-export.html http://www.sql-workbench.net/manual/command-import.html If you have both systems up and running you can directly copy the data from one table to another without the need for an intermediate file http://www.sql-workbench.net/manual/command-copy.html The import won't be as fast as Postgres' native copy command but it is usable I think. Everyting can be put into a script and run through a shell script/batch file if needed to automate the migration. Regards Thomas