Is it safe to use default value with not null when adding a new column?

All we need is an easy explanation of the problem, so here it is.

We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below:

ALTER TABLE "blogs" ADD "published" boolean DEFAULT FALSE NOT NULL

I know adding a new column with a default value is safe. However, is it still safe when combined with NOT NULL? Or will it lock the database? Thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

That statement is safe.

An ALTER TABLE will never lock "the database", it will only lock the table.

Postgres will not actually rewrite the table because you provided a constant value (false) for the default value. So adding the column is actually done in a few milliseconds because Postgres only stores the information that a new column is available. When that column is accessed (and no value is available) then it will use the default value from the column’s definition. Only when a row is updated, the new row will contain an actual value for the column.

If the default value was an expression that could potentially be different for each row, Postgres would rewrite the table to physically put the default column into all rows. But still, that would only lock the table, not the database.

From Postgres documentation about ALTER TABLE: "When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default is evaluated at the time of the statement and the result stored in the table’s metadata. That value will be used for the column for all existing rows. If no DEFAULT is specified, NULL is used. In neither case is a rewrite of the table required."

And tested in Postgres v.11: dbfiddle.uk

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply