Re: Update Default (was: Touch row ?)

Brendan Jurd <blakjak(at)blakjak(dot)sytes(dot)net> writes:
> 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;

How is #2 different from your "slightly different approach"?

> 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.

That exists already (and is SQL-standard), but I'm not convinced that
it does the job conveniently. In the example of a time-of-last-change
column, you do not want the user to have to remember to write
SET modtime = DEFAULT. In fact, you really don't want ordinary users to
be able to set the column at all. If we had per-column privilege
controls (which the spec says we should, and I think we will eventually)
then disallowing write of the modtime column to ordinary users, along
with an update default expression, would get the job done very nicely.

regards, tom lane