Databases · Middle · Question 38 of 75

How do you add a `NOT NULL` column with a default to a huge table without long locks in modern PostgreSQL?

1Add the column, then restart the server
✓Correct answer: ADD COLUMN ... DEFAULT <constant> NOT NULL is a metadata-only change since PostgreSQL 11, so it's fast
3It's impossible; you must rebuild the table

Explanation

Volatile defaults like random() still rewrite the table. For computed values, add a nullable column, backfill in batches, then add the constraint.

Further reading

Test yourself

Take the Databases · Middle quiz: questions and options are shuffled, and you get a review of your mistakes at the end.