Re: mysql to Pgsql

>>>>> "BM" == Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)kciLink(dot)com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/