Ivan Kalita:

I have a table in my PostgreSQL 9.6 database with 3 million rows. This table already has a null bitmap (it has 2 other DEFAULT NULL fields). I want to add a new boolean nullable column to this table. I stuck with the difference between these two statements:

ALTER TABLE my_table ADD COLUMN my_column BOOLEAN;
ALTER TABLE my_table ADD COLUMN my_column BOOLEAN DEFAULT NULL;

I think that these statements have no difference, but:

1. I can't find any proof of it in documentation. Documentation tells that providing DEFAULT value for the new column makes PostgreSQL to rewrite all the tuples, but I don't think that it's true for this case, cause default value is NULL.
2. I ran some tests on copy of this table, and the first statement (without DEFAULT NULL) took a little bit more time than the second. I can't understand why.

My questions are:

1. Will PostgreSQL use the same lock type (ACCESS EXCLUSIVE) for those two statements?
2. Will PostgreSQL rewrite all tuples to add NULL value to every of them in case that I use DEFAULT NULL?
3. Are there any difference between those two statements?