Search code examples
postgresqlalter-tablenotnull

Why default is not used when I set not null contratint?

I have table in postgresql database.

For given column I set default value, then I want it to be NOT NULL:

ALTER TABLE "order" ALTER COLUMN last_bill_date SET DEFAULT '-Infinity';
ALTER TABLE "order" ALTER COLUMN last_bill_date SET NOT NULL;

But second statement fails:

ERROR:  column "last_bill_date" contains null values

Why DEFAULT value is not used when NOT NULL is applied for this column?

Solution

  • Per the documentation:

    DEFAULT default_expr

    (...)

    The default expression will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null.

    The altered default expression cannot modify rows already existing in the table, you should do it before setting the not null constraint:

    update "order"
set last_bill_date = '-Infinity'
where last_bill_date is null