5
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?

0
6

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.

3
  • 1
    Actually, the core answer is not exactly true. Oct 29 '19 at 7:37
  • @ErwinBrandstetter: good catch, thanks. I amended my answer. Oct 29 '19 at 7:39
  • The answer is not 100% correct. Adding a DEFAULT NULL will still lock your table while omitting it will not, this is the case with 9.6. We've had a small incident because of that.
    – DevNG
    Jun 19 '20 at 14:25
3

No. It's not completely the same. NULL is just the default default (sic!) of data types.

All built-in data types shipped with Postgres 12 have NULL as default1. But that does not necessarily apply to all types. CREATE TYPE and CREATE DOMAIN offer to set any default, which applies to all table column of that type unless an explicit DEFAULT is set.

1 Except for one exotic exception: a data type shipped with the information schema and typically not visible in the schema search path: information_schema.time_stamp, which defaults to ('now'::text)::timestamp(2) with time zone.

Demonstrating with a domain as that's shorter. But the same applies to non-domain data types:

CREATE DOMAIN text_x AS TEXT DEFAULT 'X';

CREATE TABLE tbl (
   id int
 , col_without_default text_x
 , col_with_default    text_x DEFAULT NULL
);

The default value of col_without_default is 'X', while the one of col_with_default is NULL.

INSERT INTO tbl(id) VALUES (1) RETURNING *;
id | col_without_default | col_with_default
-: | :------------------ | :---------------
 1 | X                   | null            

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.