Re: BUG #14791: Error 42P07 but the relation DOESN'T Exists! Error 42P01

jfblazquez(dot)ayesa(at)gmail(dot)com writes:
> TEST=# CREATE TABLE scada_equipment_instance
> ...
> TEST(# CONSTRAINT scada_equipment_instance UNIQUE
> ...
> ERROR: la relación «scada_equipment_instance» ya existe

The problem here is that that unique constraint has to have an underlying
index, and the index will be named the same as the constraint, causing it
to collide with the table name. So this error is really coming from
the implied CREATE INDEX command: it sees a conflicting relation name
already in place. After the whole command rolls back, of course you
have no table either, so this is unsurprising:

> TEST=# ALTER TABLE scada_equipment_instance
> TEST-# OWNER TO postgres;
> ERROR: no existe la relación «scada_equipment_instance»

Short answer is that unique/pkey constraints can't be named the same as
any table in the same schema. Personally I'd leave off the "CONSTRAINT
name" part altogether and let the system pick a nonconflicting index name.

(Not sure that our documentation is sufficiently clear on this.
Since it's not really what you'd expect from reading the SQL standard,
maybe we need to mention it in more places than we do now.)

regards, tom lane