On 2/2/07, Jim C. <[EMAIL PROTECTED]> wrote:
I'm having to move some data from MySQL to Postgres. I used mysqldump
--compatible=postgresql, but the compatibility is extremely lacking.
It looks more like the person that designed the schema has payed very
little attention to the SQL standard. You can not blame anyone but the
designer for naming a field 'role' (which is a keyword in the SQL
standard) or using a non-standard set field type instead of a proper
lookup table.
> CREATE TABLE "credits" (
> "person" integer NOT NULL default '0',
> "chanid" int NOT NULL default '0',
> "starttime" timestamp NOT NULL default '1970-01-01 00:00:00+00',
> "role"
set('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest')
NOT NULL default ''
> -- CONSTRAINT "chanid_constraint0" UNIQUE
("chanid","starttime","person","role")
> -- UNIQUE KEY "chanid" ("chanid","starttime","person","role"),
> -- KEY "person" ("person","role")
> );
CREATE TABLE "credits" (
"person" integer NOT NULL default '0',
"chanid" int NOT NULL default '0',
"starttime" timestamp NOT NULL default '1970-01-01 00:00:00+00',
"role" VARCHAR NOT NULL,
CONSTRAINT role_check CHECK "role" IN
('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest'),
-- CONSTRAINT "chanid_constraint0" UNIQUE
("chanid","starttime","person","role")
-- UNIQUE KEY "chanid" ("chanid","starttime","person","role"),
-- KEY "person" ("person","role")
);
If that doesn't work, rename the role field. And it might be a good
idea to give this table a primary key.
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
