Setting NOT NULL in PostgreSQL

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
    ;