Re: renaming a table, and its primary key constraint

"Jim" <jhefferon(at)smcvt(dot)edu> writes:
> The table "stuff_tmp" has a primary key constraint. When I rename the
> table, this constraint does not get renamed-- it continues to have the
> name "stuff_tmp_pkey"-- and (you guessed it) the next time I run the
> script pg complains that it can't make "stuff_tmp" because the
> constraint already exists.

If you can update to PG 8.0, this problem should pretty much go away,
since it chooses nonconflicting names by default:

regression=# create table stuff_tmp(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "stuff_tmp_pkey" for table "stuff_tmp"
CREATE TABLE
regression=# alter table stuff_tmp rename to stuff;
ALTER TABLE
regression=# create table stuff_tmp(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "stuff_tmp_pkey1" for table "stuff_tmp"
CREATE TABLE
regression=#

> That I can see, I can't rename the constraint. Do I have that correct?

Yes, but you can rename the underlying index (use ALTER TABLE for this).

> So I thought to drop the constraint. That I can see I can't add a
> primary key constraint "stuff_pkey". Is that correct?

No. Try "alter table t add constraint foo primary key(f1)"

> Can I simulate (sort of) a primary key constraint by adding a UNIQUE
> index, and a NOT NULL check? That is, if I add those two, do I lose
> anything compared with the original primary key constraint?

It doesn't create a default REFERENCES target for foreign keys; which
may or may not be important to you. If it is, the whole idea won't
work at all, because dropping a table and renaming another one into
its place isn't going to cause foreign key references to transfer over.

There are other hazards involved in the idea, too, due to the fact that
cached plans won't transfer over. Which in particular means that
plpgsql functions using the table are likely to fail.

Depending on how big the table is, you might be better off with
... compute new data in stuff_tmp ...
BEGIN;
TRUNCATE TABLE stuff; -- acquires exclusive lock
INSERT INTO stuff SELECT * FROM stuff_tmp;
COMMIT;
Because of the lock, the intermediate state with no data isn't
visible to other transactions; the only effect will be a delay
until they can get at the table. (Note that the equivalent effect
would be hard to get in a RENAME-based solution, because in that
case you don't have a single continuously-existing table you can
use a lock on.)

regards, tom lane