I've got a table in which one column was defined like this:
owner_status varchar(10) check (owner_status IN ('pending','active','suspended','terminated')) NOT NULL default
'pending'
Now, I needed to have one additional value in the IN constraint. So,
first I tried altering the rcsrc attribute for this constraint in
pg_relcheck. That didn't work. So I figured I also need to alter the
rcbin column for the constraint. I created a new table with the same
column definition as above, assigned the rcbin and rcsrc values from
the new constratint to the older one. This allowed me to do the
necessary insert with the new value for owner_status.
Basically I did this, once I created the new table with the same
field, adding my new constraint:
update pg_relcheck set rcbin=(select rcbin from pg_relcheck where rcrelid=42335 and rcname='blarg_owner_status') where
rcrelid=42319and rcname='owner_info_owner_status';
update pg_relcheck set rcsrc=(select rcsrc from pg_relcheck where rcrelid=42335 and rcname='blarg_owner_status') where
rcrelid=42319and rcname='owner_info_owner_status';
Where 42319 was the relid for constraint on the original table
(owner_info), and 42335 is the relid for the constraint on the new
"template" table (blarg).
My question: is this type of surgery sufficient and safe to alter the
constraints? Is there some other place I need to diddle with as well?
I'd like to know before I actually do this on my production server
with gobs of data on it. It seems to work ok on my development
machine.
Thanks.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/