1

I have a constraint that ensures one of three columns is populated, and I'm adding a new column, so I'm replacing the old constraint with a four-column version. Since I know from the old constraint that one out of three columns must be populated, I can be sure that one out four will be populated and the new constraint is valid.

I know I can add it as NOT VALID and later validate it concurrently, and that's the kosher thing to do, but it's a bit of a waste of time for Postgres to validate all 5 billion valid rows. Can I mark it as valid without checking all the rows again? Alternatively, is there any downside to leaving it invalid indefinitely?

1

You can safely leave the check constraint invalid, if that doesn't bother you. I cannot think of any place where a check constraint is used for query planning, and it will be enforced for data modifications.

It should be safe to set convalidated to TRUE in pg_constraint in this case, but since you get no actual benefit from it, why do it?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.