From postgreSql dump to MySql...

[crixo]

How can convert a postgres sql dump script to a valid script for MySql??

I use pg_dump to generate a sql script and ai want convert this to load my db on mySql...

Exist a tools that can make this??

 

[ceco]

you can convert only the db as schema, and the data in it. the things like views, triggers, rules etc. are not available in mysql so not portable.

for the simple create table, and insert commands you can try to type your own script that simply replaces the things that do not match in mysql - for example the serial type to become int auto_increment and so on (just run and check the errors and repair them, and you should know if the thing can be done in mysql, so not trying to do something impossible) you can use the mysql's replace program to do the replaces more easily

Not very usefull I guess, but I don't know other way, but also have never needed this thing

 

[crixo]

I found a software that make postgres dump...
That's the sql generated:
CREATE TABLE "amici" (
"id" INTEGER NOT NULL,
"versione" CHAR(3) NOT NULL,
"titolo" VARCHAR(50) DEFAULT '',
"testo" TEXT DEFAULT '',
UNIQUE("id", "versione&quot
) WITH OIDS;
-----
Belowe it's the sscript generated from pg_dump
-----
CREATE TABLE "amici" (
"id" integer NOT NULL,
"versione" character(3) NOT NULL,
"titolo" character varying(50) DEFAULT '',
"testo" text DEFAULT ''
);
------
Which paramter i have to use with pg_dump to have the same result obteined with the software??

I want have a script with a type and sql syntax like in the first exaple... so my porting on MySql will be easyer...

 

[romulusnr]

I am trying to do this same thing. I have mostly given up on a conversion solution and will probably do conversion by hand.

Mysql 4.1 has aliases for SERIAL and CHARACTER types from postgres. It should accept (at very least not puke on) most of your postgres CONSTRAINTs. It correctly honors ADD CONSTRAINT PRIMARY KEY at least.

Some tips:

pg_dump -d will force data dump in standard INSERT style, rather than postgres-specific COPY style.

pg_dump tries to protect certain restricted words in names by encasing them in "double quotes". remove these double quotes from table, column, and other object names -- mysql won't like them.

Rename any of your CONSTRAINTs, especially those that are named with the postgres default names (%1, %2 etc.).

If you are porting your front-end application to use mysql instead of postgres, keep in mind that MySQL 4.1 will turn any BOOLEAN types to TINYINT. Your application then may need to be changed to test if those fields are 1 or 0 instead of 't' or 'f' as with postgres.