Re: Updating column on row update

On Sunday 22 November 2009 12:09:04 pm Thom Brown wrote:
> 2009/11/22 Aaron Burnett <aburnett(at)bzzagent(dot)com>
>
> > this is how I do it if this helps:
> >
> > column_name timestamp without time zone NOT NULL DEFAULT
> > ('now'::text)::timestamp(6) without time zone
> >
> > Hi Aaron. Thanks for the reply, but that would only insert the current
>
> date upon insertion into the table, not when the row is updated.
>
> For example
>
> CREATE TABLE timetest(
> id SERIAL NOT NULL,
> stuff text,
> stamp timestamp NOT NULL DEFAULT now()
> );
>
> INSERT INTO timetest (stuff) VALUES ('meow');
>
> The table would contain:
>
> id | stuff | stamp
> ----+-------+----------------------------
> 1 | meow | 2009-11-22 20:04:51.261739
>
> But then I'd execute:
>
> UPDATE timetest SET stuff = 'bark' WHERE id = 1;
>
> id | stuff | stamp
> ----+-------+----------------------------
> 1 | bark | 2009-11-22 20:04:51.261739
>
> You can see the time hasn't changed. But I'd want that stamp column to
> update to the current time without referring to that column in the update
> statement.
>
> Thanks
>
> Thom

You will need to use an UPDATE trigger with associated function.

--
Adrian Klaver
aklaver(at)comcast(dot)net