I have a large table (1–2m rows) and need to add a column. It is an integer column with a default of
0. I am also adding a check constraint that
column >= 0.
Currently, I am making the change in these steps:
- Add the column with the default
- Add a check constraint with
NOT VALIDso it doesn't lock the table
A coworker brought up that Postgres could be smart enough to know that adding a column with a default and then, in the same transaction, immediately adding the check constraint. Since the default passes the check constraint, all rows are guaranteed (I think?) to pass and so it wouldn't need to do a full table scan.
Is that correct? Are there better ways of doing this?