Mysql – How to convert PostgreSQL to MySQL

MySQLpostgresql

How can I convert database from PostgreSQL to MySQL?

I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it produces this error:

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the right
  syntax to use near 'bigserial NOT NULL,
    created timestamp NOT NULL,
    sql0 varchar(250) DEFAUL' at line 2

Best Answer

Once you've exported the SQL script, you need to convert the data types. There is no generic and automatic way of doing this, since MySQL and PostgreSQL have different sets of features. (PostgreSQL tends to be more standards-compliant than MySQL and may also support more features in general, e.g. CTE, indices on functions, ...)

The closest type to PostgreSQL's BIGSERIAL in the error you get is probably BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE in MySQL.

Related Solutions

Mysql – Why can’t I view the plan for view creation…

EXPLAIN shows an execution plan for SELECT queries. In version 5.6, it was extended to include plans for INSERT, UPDATE, DELETE and REPLACE statements.

No version of MySQL (not even the still in development, 5.7) has EXPLAIN for CREATE TABLE or CREATE VIEW.

You can of course run the explain for the select (that defines the view):

EXPLAIN EXTENDED
    select intercom1,phone from fisgen where fid='f105';

or as @jynus suggested (thank you), the more useful:

EXPLAIN EXTENDED
    select * from fisgen_temptable;

MySQL – How to Solve ERROR 1064 (42000)

(sequel CHAR)
CONCAT(@sequel)

@variables are totally independent of non-@ variables.

Two fixes needed:

SET @SQL = CONCAT(@sequel); --> SET @SQL = sequel;
(sequel CHAR) --> (sequel VARCHAR(999))

Best Answer

Related Solutions

Mysql – Why can’t I view the plan for view creation…

MySQL – How to Solve ERROR 1064 (42000)

Related Question