Update Default (was: Touch row ?)

Tom Lane wrote:
<snip>

>A different tack that might be interesting to think about is to invent
>a notion of an "update default" for a column, analogous to the existing
>"insert default". The normal behavior is that the "update default" is
>the old value, but if you could specify some computable expression to
>use instead, this and related problems could be solved with a much
>simpler mechanism than a rule.
>
> regards, tom lane
>
>
>
</snip>

I think the idea of the update default has interesting possbilities.
Perhaps what is needed is two classes of defaults.

1. "implicit default" -- any updates to a tuple either not specifying a
value for the target column at all, or specifying DEFAULT will set that
column to the default. This would be useful for our "touch row" or
"last modified" scenario, as discussed in the previous thread.

2. "explicit default" -- this default can only be actioned if requested
deliberately by the user. e.g. UPDATE foo SET a='x', b='y', c=DEFAULT;

A slightly different approach would be to not have explicit update
defaults at all, and instead make statements like UPDATE foo SET
c=DEFAULT actually set c to the "insert default" value. I suppose this
decision hinges on whether there are a significant set of cases where
you would want your explicit update default to be different from your
insert default.

I would tentatively suggest that (2) be the default for update defaults,
since the implicit version could generate some unexpected, and possibly
data-destructive, results if not used carefully. My idea of the column
definition syntax would be something like:

1. t timestamp NOT NULL DEFAULT NOW() UPDEF NOW() IMPLICIT;
2. c int NOT NULL UPDEF 100;

Cheers

BJ

>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>