Is DEFAULT NULL and 'nullable value without default' completely the same?

ALTER TABLE test_table ADD COLUMN a DEFAULT NULL;

vs.

ALTER TABLE test_table ADD COLUMN a;

Both columns will set NULL if column a is not specified.

As far as I know, if I add a column into a table with a default value in production database, it could lead to trouble rewriting all rows with default value.

Is DEFAULT NULL the same?

Not providing any default value or explicitly providing NULL is exactly the same thing for base data types.

Adding a column with a NULL default value, does not require rewriting the entire table. It is essentially only a metadata update for the table.

Quote from the 9.6 manual


If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead

Note that this has changed with Postgres 11.

When you add a column with a static default (e.g. default 42) the table won't be re-written either. Only if you provide a "volatile" default (e.g. current_timestamp) the table will be re-written.

StackExchange Database Administrators Q#251217, answer score: 10