Converting Postgresql table into Mysql table

Question

I have a table polit_activity with schema as:

CREATE TABLE polit_activity
(
 id serial NOT NULL,
 category_name character varying,
 soi character varying,
 heading character varying,
 description character varying,
 event_loc character varying,
 relloc character varying,
 news_date timestamp with time zone,
 sectors character varying,
 results character varying,
 remarks character varying,
 recovery character varying,
 oth_pers character varying,
 mil_units character varying,
 mil_ops_cdr character varying,
 gp_org_cdr character varying,
 gr character varying
)
WITH (
 OIDS=FALSE
);

Now I want the same table with same data in Mysql Database. I tried the commands :

psql> \o /var/lib/abc.csv
psql> select * from polit_activity;

I am unable to load that file in Mysql . Please help me with a easiest way to do this.

Please check the attached csv file

Thanks & best Regards, Adarsh Sharma

Answer 1

At the very least, you'll need to edit the SQL file you extracted from PostgreSQL. PostgreSQL and MySQL have many features in common, but different ways of using those features.

For one example, PostgreSQL and MySQL both support auto-incrementing id numbers, but they use different reserved words and technologies to implement those id numbers. PostgreSQL uses the reserved word "SERIAL" as a part of the DDL in that process; MySQL doesn't.

If the PostgreSQL file uses any of MySQL's reserved words as a table or column name, you'll need to guard it with backticks. Backticks aren't legal in PostgreSQL.

If I had just one table to deal with, I'd probably just

• rewrite the DDL to conform to MySQL's requirements,
• dump the data (not the schema) to disk using pg_dump,
• and read the data into the new MySQL table.