Re: [GENERAL] Update Default

[Brendan Jurd]

Tom Lane wrote: Brendan Jurd <[EMAIL PROTECTED]> 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"? Ah, sorry if this was unclear.  #2 would mean that the column definition has an explicit "update default", which could potentially be different from the "insert default", if that was desired.  The "slightly different approach" would mean that explicit SET to DEFAULT instructions would just use the insert default.  I was just unsure whether it would be useful in practice to have separate values for the explicit update default and the insert 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. 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. Agreed, UPDATE SET x = DEFAULT isn't a good solution for the last modtime column.  But that doesn't mean it wouldn't be useful in other situations. 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. Sounds good -- column based privileges would have a lot of handy applications. Cheers BJ regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend