Re: timestamp

Inserting is easy:

create table timestamped
(
id serial primary key,
stuff text,
stamp timestamp not null default now ()
);

insert (stuff) into timestamped values ('blah');

Updating is more tricky... One way is to use a rule:

create rule update_stamp as on update to timestamped where stamp is null do instead
update timestamped set stuff = new.stuff, timestamp=now() where id = new.id;

or

create rule update_replace as on update to timestamped do instead
(
delete from timestamped where id=old.id;
insert into timestamped values (new.*);
);

I like this one better, because it does not depend on any column names, except the it - so you can modify the original table,
but the rule will still work...

Another way is to create a trigger:

create function update_stamp () returns opaque as
'begin; new.stamp=now(); return new;end;'
language 'plpgsql';

create trigger update_stamp_tg before update on timestamped
for each row execute procedure update_stamp();

This is even better than the rule, because you can use that same function with any table that has a column, called 'stamp',
even if it doesn't have a primary key
You can also make the column name an argument,for complete flexibility but I don't know how to do that in plpgsql (I believe, it's possible though) -
can be fairly easily done in C if you are not afraid of writing C functions :-)

I hope, it helps...

Dima

Daniel Jaenecke wrote:
> Hi!
>
> I am rather new to PostGreSQL, having mainly used MySQL until now.
> MySQL has a column type TIMESTAMP[1] which holds - as expected - a timestamp, but additionally this timestamp is being updated automatically on any UPDATE or INSERT operation.
>
> Since this is a quite handy feature to keep track of latest changes I would like to create such a behavior for my PostGres tables too. But since I have no idea how to do it I would warml welcome any suggestion... :)
>
> Thx
> dj
>
> [1]
> http://www.mysql.com/doc/en/DATETIME.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster