Set postgres column to not null with default without update

Question

Is there a way to add a not null constraint to a column and replace all existing null values with a default in one statement?

alter table t
alter column c set default 0,
alter column c set not null;

Doesn't seem to work, gives an error:

column "c" contains null values

Answer 1

Actually, yes. Pretty simple, too:

ALTER TABLE t
  ALTER COLUMN c TYPE int USING (COALESCE(c, 0))
, ALTER COLUMN c SET DEFAULT 0
, ALTER COLUMN c SET NOT NULL;

db<>fiddle here

You just have to think around a corner. We change the type from int to int, so no actual change. But it allows us to slip in the USING clause that does the magic.