2

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?

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158

1 Answers1

2

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
klin
  • 112,967
  • 15
  • 204
  • 232
  • You shouldn't call it a constraint. – Laurenz Albe Oct 24 '18 at 10:11
  • 1
    @LaurenzAlbe - opinions are divided [SQL DEFAULT Constraint](https://www.w3schools.com/sql/sql_default.asp) – klin Oct 24 '18 at 10:17
  • I see and am surprised. I'd refuse to call something a constraint if it does not constrain anything. – Laurenz Albe Oct 24 '18 at 10:28
  • I agree that strict semantics indicate your point of view. I guess the term is quite popular because of its conciseness and ease of use. – klin Oct 24 '18 at 10:45
  • @klin: I read that postgresql (possibly 11?) can do lazy update for altered columns. And that lazy uses `default` value. – Eugen Konkov Oct 24 '18 at 12:34
  • @EugenKonkov - AFAIK there is an improvement with not null default in pg11 but it concerns `alter table add column ...` – klin Oct 24 '18 at 12:54