BlogTwitterGithubEmailRSS

Racheting 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.