Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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 ;
share|improve this question

2 Answers 2

The simplest thing to do is not specify a name at all (e.g. "CREATE INDEX ON Pages (SiteId)"); Postgres will make up a distinct name. Otherwise make up distinct names yourself (e.g. "CREATE INDEX idx_Pages_SiteId ON ...")

share|improve this answer
    
But I thought an index name was used by the code that operates on the table. If this is the case, then it may not operate as expected. But this may also reveal my lack of understanding of indexes. –  user3716906 Jun 7 at 3:07
2  
Nope, the index name is used only for the database's internal use, and for if you want to mention the index for drop/create statements etc. –  Craig Ringer Jun 7 at 5:52

As Craig said, indexes are only used internally to the database so the index names are not important - but in PostgreSQL they must be unique.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.