Skip site navigation (1) Skip section navigation (2)

Re: Update Default

From: Brendan Jurd <blakjak(at)blakjak(dot)sytes(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Eric B(dot)Ridge" <ebr(at)tcdi(dot)com>,Chris Travers <chris(at)travelamericas(dot)com>,Jan Wieck <JanWieck(at)Yahoo(dot)com>, NTPT <ntpt(at)centrum(dot)cz>,Mike Mascari <mascarm(at)mascari(dot)com>,PostgreSQL-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Update Default
Date: 2004-01-29 19:36:25
Message-ID: 401960B9.7030007@blakjak.sytes.net (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-general
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
<br>
Tom Lane wrote:<br>
<blockquote type="cite" cite="mid5288(dot)1075256131(at)sss(dot)pgh(dot)pa(dot)us">
  <pre wrap="">Brendan Jurd <a class="moz-txt-link-rfc2396E" href="mailto:blakjak(at)blakjak(dot)sytes(dot)net">&lt;blakjak(at)blakjak(dot)sytes(dot)net&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">I think the idea of the update default has interesting possbilities.  
Perhaps what is needed is two classes of defaults. 
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
  <blockquote type="cite">
    <pre wrap="">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;
    </pre>
  </blockquote>
  <pre wrap=""><!---->
How is #2 different from your "slightly different approach"?

  </pre>
</blockquote>
Ah, sorry if this was unclear.&nbsp; #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.&nbsp; The
"slightly different approach" would mean that explicit SET to DEFAULT
instructions would just use the insert default.&nbsp; I was just unsure
whether it would be useful in practice to have separate values for the
explicit update default and the insert default.<br>
<blockquote type="cite" cite="mid5288(dot)1075256131(at)sss(dot)pgh(dot)pa(dot)us">
  <pre wrap=""></pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.  </pre>
</blockquote>
Agreed, UPDATE SET x = DEFAULT isn't a good solution for the last
modtime column.&nbsp; But that doesn't mean it wouldn't be useful in other
situations.<br>
<br>
<blockquote type="cite" cite="mid5288(dot)1075256131(at)sss(dot)pgh(dot)pa(dot)us">
  <pre wrap="">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.

  </pre>
</blockquote>
Sounds good -- column based privileges would have a lot of handy
applications.<br>
<br>
Cheers<br>
<br>
BJ<br>
<blockquote type="cite" cite="mid5288(dot)1075256131(at)sss(dot)pgh(dot)pa(dot)us">
  <pre wrap="">			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
  </pre>
</blockquote>
</body>
</html>



Attachment: unknown_filename
Description: text/html (3.4 KB)

In response to

pgsql-announce by date

Next:From: Robert TreatDate: 2004-02-03 16:31:32
Subject: == PostgreSQL Weekly News - Februrary 2nd 2004 ==
Previous:From: Jose Paulo Batista SilvaDate: 2004-01-28 11:38:09
Subject: Re: [GENERAL] Looking for mail relays ...

pgsql-general by date

Next:From: Martin MarquesDate: 2004-01-29 19:45:59
Subject: Re: Grant question - More
Previous:From: Jack OrensteinDate: 2004-01-29 19:28:00
Subject: Viewing detailed lock information

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group