Ratcheting with Postgres CONSTRAINT
I was inspired by this article to think about how I could iteratively improve the column invariants in the database I was working on, without being overwhelmed by having to backfill everything at once (e.g. in the case where we want to force a column to be NOT NULL
, and then call ALTER COLUMN SET NOT NULL
).
After some brief research I was pleased to discover that Postgres has this as a feature - CONSTRAINT
with the NOT VALID
option. For example, if I want to force new inserts and updates to table foo to have non null column bar, I can add:
ALTER TABLE foo
ADD CONSTRAINT bar_not_null
CHECK (bar IS NOT NULL) NOT VALID;
and later once the null values have been backfilled with non nulls, we can drop the constraint and add the harder ALTER COLUMN SET NOT NULL
.