Set postgres column to not null with default without update-CodePudding

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

CodePudding user response：

You can do:

alter table t
alter column a type int using coalesce(a, 567),
alter column a set not null;

See running example at DB Fiddle.

CodePudding user response：

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.