4

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
kag0
  • 5,624
  • 7
  • 34
  • 67

2 Answers2

5

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    And it's even faster than using a separate `UPDATE` statement. Just measured `USING` approach is 6 times faster than `UPDATE+ALTER` on 8M rows (no index on changed field) – pavelety Sep 23 '22 at 23:02
  • 1
    ⚠️This approach **locks the whole table** until it finished. If you can, better use slower `UPDATE`, but without a lock. – pavelety Oct 04 '22 at 19:16
0

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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76