Hello
Correct index lookup is a difficult task. I tried to implement this previously...
But the answer in SO is a bit incomplete for recent postgresql releases. Seqscan is not the only possible way to set
notnull in pg12+. My patch was commited ( https://commitfest.postgresql.org/22/1389/ ) and now it's possible to do this
way:
alter table foos
add constraint foos_not_null
check (bar1 is not null) not valid; -- short-time exclusive lock
alter table foos validate constraint foos_not_null; -- still seqscan entire table but without exclusive lock
An then another short lock:
alter table foos alter column bar1 set not null;
alter table foos drop constraint foos_not_null;
regards, Sergei
