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

Environment specific issue where there are only permissions to modify the schema, not the data. — kag0
– kag0, Commented Feb 5, 2022 at 2:20

Erwin Brandstetter · Accepted Answer · 2022-02-05 02:39:13Z

12

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.

edited Feb 5, 2022 at 2:39

answered Feb 5, 2022 at 2:34

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pavelety Over a year ago

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 Over a year ago

⚠️This approach locks the whole table until it finished. If you can, better use slower UPDATE, but without a lock.

The Impaler · Accepted Answer · 2022-02-05 02:35:45Z

1

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.

answered Feb 5, 2022 at 2:35

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Collectives™ on Stack Overflow

Set postgres column to not null with default without update

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related