I'm a programmer specialising in performant and scalable systems using PHP and Ruby and cooking


Published:
On PHP | PostgreSQL

PostgreSQL indexes must be unique!

Recently while converting a site from using MySQL to PostgreSQL I found an annoyling abscure problem I dind't realise would ever be an issue.

The site in question use Doctrine2 withing Symfony2 and had 2 previously working tables, products and manufacturers.

These tables had an index on the name column configured like this:

/**
 * @ORM\Entity
 * @ORM\Table(name="product_manufacturers", indexes={
 *      @ORM\index(name="name_idx", columns={"name"})
 * })
 */

/**
 * @ORM\Entity
 * @ORM\Table(name="product_products", indexes={
 *      @ORM\index(name="name_idx", columns={"name"}),
 *      @ORM\index(name="hash_idx", columns={"hash"})
 * })
 */

In MySQL this isn't a problem. However when creating the schema in PostgreSQL I was receiving this error:

SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "name_idx" already exists  

This stumped me, however after a quick test I found that the index name has to be unique accross tables.

So now my indexes are like this:

/**
 * @ORM\Entity
 * @ORM\Table(name="product_manufacturers", indexes={
 *      @ORM\index(name="manufacturer_name_idx", columns={"name"})
 * })
 */

/**
 * @ORM\Entity
 * @ORM\Table(name="product_products", indexes={
 *      @ORM\index(name="product_name_idx", columns={"name"}),
 *      @ORM\index(name="product_hash_idx", columns={"hash"})
 * })
 */

Be aware of the limit for index names. Most of the time (Centos 5.4 and OSX confirmed) this is 63. However if your name is longer than the limit PostgreSQL will truncate the index name and try to use that.

Hope this helps