Why not just use a DEFAULT NOW()?
ie:
CREATE TABLE blah (
time TIMESTAMP NOT NULL DEFAULT NOW()
);
I know it works for DATETIME types, don't know about TIMESTAMP but I assume
it would be the same.
Luke.
----- Original Message -----
From: "will trillich" <will@serensoft.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, January 24, 2003 7:13 AM
Subject: Re: [GENERAL] I was spoiled by the MySQL timestamp field
> On Thu, Jan 23, 2003 at 04:26:31AM -0800, Alan T. Miller wrote:
> > As someone who is just getting started with PostygreSQL from
> > years working with MySQL, it appears that the timestamp data
> > type does not behave in the way it did with MySQL. I got used
> > to just defining a column as a timestamp and letting the
> > database throw the latest time stamp in there whenever a row
> > was updated. Is there anything simular in PosgreSQL? How can I
> > accomplish something simular inside the database, or am I stuck
> > populating the field in some manner as in the following example
> >
> > update blah blah blah timestamp = NOW()
>
> triggers or rules can do that with any timestamp (or timestamp(0)
> -- no partial-seconds) field. here's a "rules" approach:
>
> create table _something (
> id serial,
> dat text,
> freshened timestamp(0),
> primary key ( id )
> );
>
> create view something as
> select
> id,
> dat,
> freshened
> from
> _something;
>
> create rule something_add as
> on insert to something
> do instead (
> insert into _something (
> --id,
> dat,
> freshened
> ) values (
> --let id take care of itself,
> NEW.dat,
> current_timestamp
> );
> );
>
> create rule something_edit as
> on update to something
> do instead (
> update _something set
> --id = leave it alone,
> dat = NEW.dat,
> freshened = current_timestamp
> where
> id = NEW.id
> ;
> );
>
> then you can just
>
> insert into something (dat) values ('yada yada');
> update something set dat = 'here we go' where id = 23978;
>
> and "freshened" takes care of itself.
>
> --
> There are 10 kinds of people:
> ones that get binary, and ones that don't.
>
> will@serensoft.com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> Looking for a firewall? Do you think smoothwall sucks? You're
> probably right... Try the folks at http://clarkconnect.org/ !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
