Re: Convert from Mysql to Postgresql

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
>> CREATE TABLE palm_addressbook (
>> cal_id int(11) NOT NULL default '0',
>> account_id int(11) NOT NULL default '0',
>> palm_rec_id int(11) NOT NULL default '0',
>> PRIMARY KEY (cal_id,account_id),
>> UNIQUE KEY account_id (account_id,palm_rec_id)
>> ) TYPE=MyISAM;

> Aieee! I had no idea that MySQL syntax was that non-standard ...

I don't think it's *that* bad. All I can see that needs to be changed
is

* int(11) is not standard; use INTEGER (standard) or INT (standard) or
INT4 (PG-ism). Or perhaps you want DECIMAL(11,0) or BIGINT (a/k/a
INT8). 11 digits seems an odd choice -- is that really an efficient
integer size for MySQL?

* UNIQUE KEY is not right, it should just be UNIQUE according to our
reading of the SQL92 spec. Does MySQL really want the word KEY there?
Also, the name of the secondary-key index cannot be specified where
you placed it; SQL92 wants a "CONSTRAINT name" clause in front of the
constraint type if you are going to give a name to the constraint.
So
CONSTRAINT account_id UNIQUE (account_id,palm_rec_id)
would be the standards-compliant (and PG-friendly) phrasing of the
last clause. Does MySQL accept that?

* TYPE=MyISAM is definitely not standard, drop it.

BTW, writing the defaults as string literals seems a tad odd for
non-string fields. As it happens, PG will take this, but I'd think that
just 0 with no quote marks would be more portable.

regards, tom lane