Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> in my audited tables I do this:
> modify_when time stamp with time zone not null default CURRENT_TIMESTAMP check(modify_when=now()),
> This works on insert. However, on update a function runs
> via a trigger but fails with:
> ExecReplace: rejected due to CHECK constraint audit_mark_modify_when
Well, yeah. A default is computed on insert, but it has nothing to do
with updates. The above would essentially force all updates to
explicitly include "SET modify_when = now()", or the check condition
would fail.
The best way to achieve the effect you want is probably with a BEFORE
INSERT OR UPDATE trigger that explicitly sets NEW.modify_when = now().
Having done that, you don't need either the default or the check,
because there is no way to override the trigger's action (except with
another trigger).
regards, tom lane
