Re: forcing a literal value in a column

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