Thread: Bug #895: incorrect error message when duplicate index name

VIacheslav N Tararin (taras@dekasoft.com.ua) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
incorrect error message when duplicate index name

Long Description
ERROR:  relation named "pk_exchange_batch_idx" already exists
Why relation when I create index?


Sample Code
create unique index PK_EXCHANGE_BATCH_IDX   on CURRENCY.EXCHANGE_BATCH(ID);
create        index PK_EXCHANGE_BATCH_IDX   on CURRENCY.EXCHANGE_BATCH(TYPE_ID, FROM_DATE, TO_DATE);


No file was uploaded with this report

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Long Description
> ERROR:  relation named "pk_exchange_batch_idx" already exists
> Why relation when I create index?

An index is a type of relation: other types of relations include tables,
views, and sequences. A relation is simply something in the database that
can be represented as a table. Each relations must have a unique name, no
matter what type it is:

=> create table foobar(a int);
CREATE TABLE

=> create index foobar on foobar(a);
ERROR:  relation named "foobar" already exists

=> create view foobar as select * from foobar;
ERROR:  Relation 'foobar' already exists


You can see from the last example why having the same name would be a
problem - does "SELECT * FROM foobar" refer to the table 'foobar'
or the view 'foobar'? If we did not have the unique name contraint,
we would have to tell the database which 'foobar' we meant.

The error message you received means that *something* else already
has that name, but not necessarily an index.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302130948
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+S7B4vJuQZxSWSsgRAluSAJ9lLglRRijBRhCbIh6/v5pQm0U1zACgjIKz
/AZ9yAdOsZ2xoF2xwp/YKS8=
=VW2o
-----END PGP SIGNATURE-----