PostgreSQL. Добавляем not null constraints в большие таблицы
In PostgreSQL 12 (and above) scan the entire table is not the only option. alter table set not null
can prove correctness of NOT NULL by existing check constraints. So, one can do:
-- short-time exclusive lock
alter table foos
add constraint foos_not_null
check (bar1 is not null) not valid;
-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;
-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;
We get the similar error ERROR: column "name" of relation "users" contains null values
, therefore we need also need to amend the current NULL
values (and possibly set a default)
ALTER TABLE users
ALTER COLUMN name TYPE TEXT USING (COALESCE(name, 'Hugo')),
ALTER COLUMN name SET DEFAULT 'Hugo',
ALTER COLUMN name SET NOT NULL
;