Re: forcing a literal value in a column

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)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