Обсуждение: Convert from Mysql to Postgresql

Could someone tell me the SQL for an equivalent table in PostgreSQL?

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;


I think it would be

CREATE TABLE palm_addressbook (
  cal_id int4 NOT NULL default '0',
  account_id int4 NOT NULL default '0',
  palm_rec_id int4 NOT NULL default '0',
  PRIMARY KEY  (cal_id,account_id)
  );

Brian,

> 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 think it would be

You're close.  Use:

> CREATE TABLE palm_addressbook (
>   cal_id int4 NOT NULL default (0),
>   account_id int4 NOT NULL default (0),
>   palm_rec_id int4 NOT NULL default (0),
>   CONSTRAINT cs_palm_address_key PRIMARY KEY (cal_id,account_id)
>   );

And, hey, if you get a Palm-to-postgresql app running, send me e-mail.
  I might be able to re-sell it.

-Josh

"Josh Berkus" <josh@agliodbs.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