Thread: renaming table leaves constraint behind [7.3.2]

Hi there,

the following code throws an error (which it didn't in 7.2.x):

CREATE TABLE a ( id int unique );
...do a lot of things with a...
ALTER TABLE a RENAME to b;

CREATE TABLE a ( id int unique ); -- fails

Is there a "clean" way to have constraints renamed along with the table? Or
this there a way to just rename the constraint?

With kind regards / mit freundlichem Gruß
    Holger Klawitter
--
Holger Klawitter                          http://www.klawitter.de
lists@klawitter.de

Holger Klawitter <lists@klawitter.de> writes:
> the following code throws an error (which it didn't in 7.2.x):

Sure it did.  The error message seems to have changed a trifle, but
you'd get a conflict on the index name in either case.

This isn't real desirable ... but having ALTER TABLE RENAME run around
and rename associated indexes might create issues too ...

            regards, tom lane

Hi Tom,

thanks for your reply!

> Sure it did.  The error message seems to have changed a trifle, but
> you'd get a conflict on the index name in either case.

The error message is okay. In 7.2.x the constraint was DROP INDEXed, so it
didn't show up there.

> This isn't real desirable ... but having ALTER TABLE RENAME run around
> and rename associated indexes might create issues too ...

But in particular when names are automatically created one likes a way to
change the names of the constraints. I've found a workaround:

-- as postgres --
create view my_connames as select conname from pg_constraint;
create rule upd_connames as on update to my_connames do instead
  update pg_constraint
  set conname = new.conname where conname = old.conname;
grant select,update on my_connames to '<user>';

With kind regards / mit freundlichem Gruß
    Holger Klawitter
--
Holger Klawitter                          http://www.klawitter.de
lists@klawitter.de