Postgres Pro
Downloads
Home   >   mailing lists

Thread: mysql to Pgsql

mysql to Pgsql

From
Martín Marqués
Date:
I think I heard about a script that would convert mysql dump files to Pgsql
dumps. Can someone remind me where they are?

Saludos... :-)

--
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: mysql to Pgsql

From
Bruce Momjian
Date:
[ Charset ISO-8859-1 unsupported, converting... ]
> I think I heard about a script that would convert mysql dump files to Pgsql
> dumps. Can someone remind me where they are?
>
> Saludos... :-)

In 7.1 /contrib.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: mysql to Pgsql

From
Joel Burton
Date:
On Thu, 3 May 2001, Bruce Momjian wrote:

> [ Charset ISO-8859-1 unsupported, converting... ]
> > I think I heard about a script that would convert mysql dump files to Pgsql
> > dumps. Can someone remind me where they are?
> >
> > Saludos... :-)
>
> In 7.1 /contrib.

And more info about it at techdocs.postgresql.org.

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Re: mysql to Pgsql

From
Vivek Khera
Date:
>>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

BM> [ Charset ISO-8859-1 unsupported, converting... ]
>> I think I heard about a script that would convert mysql dump files to Pgsql
>> dumps. Can someone remind me where they are?

BM> In 7.1 /contrib.

I started with trying several scripts.  All of them leave some mysql
turds lying around after their "translation".  I ended up doing it
manually, and took some notes.   Here's what I did:

--cut here--
change # comments to -- comments

change "int(10)" to "integer"

change "integer unsigned NOT NULL auto_increment" fields to type
 "SERIAL PRIMARY KEY"

change "integer unsigned" to "integer check(colname >= 0)"

change "mediumtext" => "text"

remove "Type = XXX" table option at end of create statements.

change "timestamp(14)" => "timestamp default 'NOW()'" .. probably need
 trigger to update this kind of timestamp on every update as well.

change "datetime" => "timestamp"

move "KEY" indexes after column create with CREATE INDEX

change "enum" fields to "varchar(X) check (fn in ('...','...'))"

change "set" fields to some type of text field, and make app convert
 to/from comma separated string of values.

change "tinyint(N)" to "smallint"

change "UNIQUE KEY name" to "UNIQUE"

delete "binary" attributes to char fields.

delete any "default NULL" as they are redundant.

"text NOT NULL" needs to have "default ''" for same behavior as in mysql.


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: Re: mysql to Pgsql

From
Bruce Momjian
Date:
We really need those changes merged into the scripts for the next guy.
Ideally, I would like the two /contrib scripts merged to make a better one.


> >>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
> BM> [ Charset ISO-8859-1 unsupported, converting... ]
> >> I think I heard about a script that would convert mysql dump files to Pgsql
> >> dumps. Can someone remind me where they are?
>
> BM> In 7.1 /contrib.
>
> I started with trying several scripts.  All of them leave some mysql
> turds lying around after their "translation".  I ended up doing it
> manually, and took some notes.   Here's what I did:
>
> --cut here--
> change # comments to -- comments
>
> change "int(10)" to "integer"
>
> change "integer unsigned NOT NULL auto_increment" fields to type
>  "SERIAL PRIMARY KEY"
>
> change "integer unsigned" to "integer check(colname >= 0)"
>
> change "mediumtext" => "text"
>
> remove "Type = XXX" table option at end of create statements.
>
> change "timestamp(14)" => "timestamp default 'NOW()'" .. probably need
>  trigger to update this kind of timestamp on every update as well.
>
> change "datetime" => "timestamp"
>
> move "KEY" indexes after column create with CREATE INDEX
>
> change "enum" fields to "varchar(X) check (fn in ('...','...'))"
>
> change "set" fields to some type of text field, and make app convert
>  to/from comma separated string of values.
>
> change "tinyint(N)" to "smallint"
>
> change "UNIQUE KEY name" to "UNIQUE"
>
> delete "binary" attributes to char fields.
>
> delete any "default NULL" as they are redundant.
>
> "text NOT NULL" needs to have "default ''" for same behavior as in mysql.
>
>
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D.                Khera Communications, Inc.
> Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026