I'm converting some CREATE TABLE queries from MySQL to Postgres and have encountered an issue with creating multiple keys (aka Indexes) with the same name on different tables.
For example, in MySQL you can have multiple CREATE TABLE queries that contain the same index creation syntax: KEY OrgId
(SiteId
) - See MySQL example at bottom of this post.
In Postgres I understand you cannot define indexes in the CREATE TABLE query, so I am running two separate Postgres queries to define the indexes:
CREATE INDEX OrgId ON MenuTypes USING btree (SiteId);
CREATE INDEX OrgId ON Pages USING btree (SiteId);
While the first CREATE INDEX query is working, the second fails to run because the index name is a duplicate. (ERROR: relation "orgid" already exists)
Does anyone have any ideas on how I can get around this?
Here are the two MySQL queries I am trying to convert to Postgres:
CREATE TABLE IF NOT EXISTS `MenuTypes` (
`MenuTypeId` int(11) NOT NULL AUTO_INCREMENT,
`MenuTypeUniqId` varchar(50) NOT NULL,
...
`SiteId` int(11) NOT NULL,
...
PRIMARY KEY (`MenuTypeId`),
KEY `OrgId` (`SiteId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
CREATE TABLE IF NOT EXISTS `Pages` (
`PageId` int(11) NOT NULL AUTO_INCREMENT,
`PageUniqId` varchar(50) NOT NULL,
...
`SiteId` int(11) NOT NULL,
...
`Created` datetime NOT NULL,
PRIMARY KEY (`PageId`),
KEY `OrgId` (`SiteId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=457 ;