Re: Determining when a row was inserted

Please reply on the bottom, it makes it much easier to follow the
responses...

On Fri, 2005-06-03 at 14:05, Wiebe de Jong wrote:
> I don't use this for all tables, only the ones with important
> information in them, like people, accounts, etc.
>
>
>
> I actually have two fields, tsCreated and tsUpdated, both which
> default to now(). When I do an update, I set the value of tsUpdated to
> now(). The tsCreated field is always left alone. This way, I always
> know when the record was created and last updated.
>
>
>
> This is much simpler than using triggers to update a separate audit
> table.
>
>
>
> Wiebe
>
>
>
>
> ______________________________________________________________________
>
> From: Scott Marlowe [mailto:smarlowe(at)g2switchworks(dot)com]
> Sent: Friday, June 03, 2005 11:55 AM
> To: Wiebe de Jong
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Determining when a row was inserted
>
>
>
>
> Reply at bottom...
>
> On Fri, 2005-06-03 at 12:53, Wiebe de Jong wrote:
> > The way I do it is to add a timestamp field with a default value of
> now().
> > Unfortunately, this won't help with any records that have already
> been
> > created.
> >
> > Wiebe
> >
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Terry Lee
> Tucker
> > Sent: Thursday, June 02, 2005 5:51 AM
> > To: pgsql-general(at)postgresql(dot)org
> > Subject: Re: [GENERAL] Determining when a row was inserted
> >
> > I don't think there is a way to do that. You'll have to create an
> audit
> > table
> > and a rule to update it or you'll have to add a column to the table
> and a
> > trigger to update it.
> >
> > On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
> > > Folks - hi
> > >
> > > Is it possible to determine when a row was inserted into a table
> using the
> > > system catalogs or such. I have the situation where I need to find
> out
> > when
> > > a user was added to a user table - the table was not setup with a
> date to
> > > track this.
>
> There are plenty of examples of a trigger to do this so that ANY time
> the row is updated, or when it's inserted, the timestamp gets updated
> to
> now() or timeofday.

You don't have to have a separate audit table, and the trigger ensures
that you have the right time whether your application tried to set it
wrong or not. So, if a new user thinks he needs to set it to something,
a trigger will still set it to what it should be.