Renaming tables - Mailing list pgsql-admin
| From | Tim DeMarco |
|---|---|
| Subject | Renaming tables |
| Date | |
| Msg-id | A015F722AB845E4B8458CBABDFFE634203D29C@mail0.myrio.com Whole thread Raw |
| Responses |
Re: Renaming tables
|
| List | pgsql-admin |
Hi,
I'm running postgres 7.0.3 on Sun 220R (1Gig Ram) with Solaris2.8 (upgrading to 7.1.3 or 7.2 very soon)
I have several tables I am planning on renaming and ran into a little trouble. I know I can fix the problem, but I'm hoping there is an easier solution. Here is an example: I rename usergroupstatus to usergroupstatusvalues. I did a dump of the this table as well as a table that has a Fkey to this table:
CREATE TABLE "usergroupstatusvalues" (
"status" character varying(25) NOT NULL,
PRIMARY KEY ("status")
);
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroupstatusvalues" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroupstatusvalues" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
"status" character varying(25) NOT NULL,
PRIMARY KEY ("status")
);
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroupstatusvalues" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroupstatusvalues" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE TABLE "usergroup" (
"id" numeric(11,0),
"name" character varying(50) NOT NULL,
"groupid" character varying(2) NOT NULL,
"issystemadmin" numeric(1,0),
"lastupdatedatetime" character varying(50),
"lastupdateuserid" character varying(30),
"recordstatuscode" character varying(2) NOT NULL,
"location" character varying(5) NOT NULL,
"status" character varying(25) NOT NULL,
"timestamp" character varying(30),
PRIMARY KEY ("groupid")
);
"id" numeric(11,0),
"name" character varying(50) NOT NULL,
"groupid" character varying(2) NOT NULL,
"issystemadmin" numeric(1,0),
"lastupdatedatetime" character varying(50),
"lastupdateuserid" character varying(30),
"recordstatuscode" character varying(2) NOT NULL,
"location" character varying(5) NOT NULL,
"status" character varying(25) NOT NULL,
"timestamp" character varying(30),
PRIMARY KEY ("groupid")
);
CREATE UNIQUE INDEX "usergroup_id_unique" on "usergroup" using btree ( "id" "numeric_ops" );
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'usergroup', 'recordstatuscodes', 'UNSPECIFIED', 'recordstatuscode', 'code');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usergroupsignals', 'usergroup', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usergroupsignals', 'usergroup', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usertable', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usertable', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'securityaccessprofile', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'securityaccessprofile', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'usergroup', 'recordstatuscodes', 'UNSPECIFIED', 'recordstatuscode', 'code');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usergroupsignals', 'usergroup', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usergroupsignals', 'usergroup', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usertable', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usertable', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'securityaccessprofile', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'securityaccessprofile', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
The constraints still refer to the original table name (usergroupstatus). I know that I can drop and re-create the tables and have them refer to the new table, but I'm hoping there is a better way. Also, if I need to recreate the tables should I use the statements above (generated by pg_dump) and manually modify the constraints?
Thanks in advance!
Tim DeMarco
pgsql-admin by date: