Re: Modifying check constraints

On 15 Nov 2001, Vivek Khera wrote:

> >>>>> "SS" == Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
>
> >> Can I change the CHECK constraint afterwards? For example, can I
> >> add another value that "tipo" can have, or I'd have to recreate the
> >> table?
>
> SS> Right now, I think you'd need to recreate, but 7.2 should let you drop
> SS> and re-add the check constraint.
>
> I posted a note the other day to postgres general titled "constraint
> surgery" showing how I altered the check constraints on a table asking
> if it would cause problems. it is in the list archives for Nov 7 (I
> think).
>
> I never got any replies, so I'm getting ready to try it on my
> production server and hope it doesn't croak anything.

I'd guess so, except that it doesn't look like it necessarily takes effect
in sessions that are currently running that have already done the check
(I need to restart psql).

If you can have downtime, you might just want to look at making the
new table and using insert into to copy the data and rename the tables
around. This is kinda unhappy with foreign keys and more complicated
constructs, but in general is less likely to cause you grief.