From: Dmitry Tkach <dmitry@openratings.com>
To: Daniel Jaenecke <jaenecke@smaxs.de>
Subject: Re: timestamp
Date: Wed, 12 Feb 2003 12:55:22 -0500
Message-ID: <3E4A8A8A.5000103@openratings.com> (raw)
In-Reply-To: <20030212100915.296331a6.jaenecke@smaxs.de>
References: <20030212100915.296331a6.jaenecke@smaxs.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, jaenecke@smaxs.de
Subject: Re: timestamp
In-Reply-To: <3E4A8A8A.5000103@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox