Add NOT NULL constraint to large table without table scan Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table statement? I created an index on the column hoping it would be used but that doesn't seem to work. May be a check constraint? Other options? Thank you!
Is there a way to prevent a full table scan during the alter table statement?


At this time there is no supported, safe way to do that with PostgreSQL.

Some kind of ALTER TABLE ... ADD CONSTRAINT ... CONCURRENTLY would be nice, but nobody's implemented it. Same with the alternative of adding a NOT VALID constraint that still affects new rows, and that you then VALIDATE later - it'd be good, and it's something everyone knows is needed but nobody's had the time or funding to add yet.

In theory you could directly modify the system catalogs to add the constraint if you know it is true and valid. In practice, well, it's generally not a great idea.

So no, there isn't really a way.