Hajo@Lemcke.com writes:
> I would like to have the default value replace a given null value if the
> column is specified with not null and default.
> The SQL specification only requires to set the default value when the column
> is omitted on insert or update.
> I think this is a flaw in the specification because it requires developers
> to create multiple statements for inserts and updates for all combinations
> of default not null columns.
This may be a flaw in the specification, but you'd need to talk to the
SQL standards committee to get it changed, not us. We're quite unlikely
to change away from standard-mandated behavior.
If you really need such behavior to support broken application logic,
you can implement it with a BEFORE INSERT/UPDATE trigger.
regards, tom lane
