Postgresql: what is different between DEFAULT NULL vs NULL on alter table

Question

Is any different between this two queries? If there is what will be faster?

ALTER TABLE mytable ADD COLUMN newcolumn VARCHAR(64) DEFAULT NULL;
ALTER TABLE mytable ADD COLUMN newcolumn VARCHAR(64) NULL;

How does it work under the hood? If we have a table with a millions of rows will any one of queries rewrite every row to set NULL value to a new column?

Answer 1

https://www.postgresql.org/docs/current/static/sql-createtable.html

NULL The column is allowed to contain null values. This is the default.

and further

DEFAULT default_expr

...

If there is no default for a column, then the default is null.

so your two statements is resetting column to two default creation options.

Whichever in your column is not default will take longer over default one. If both were made not default - will take same time as it will just update catalog, changing two, one or no modifiers...

Answer 2

The first statement adds a column with a default value of NULL, meaning an insert will set null if not explicitly included in the insert.

The second statement adds a column with a “constraint” that the column value may be NULL, as the opposite of the NOT NULL constraint.