Re: Determining when a row was inserted - Mailing list pgsql-general

From Wiebe de Jong
Subject Re: Determining when a row was inserted
Date
Msg-id 0IHI00K7IVORIF@l-daemon
Whole thread Raw
In response to Re: Determining when a row was inserted  (Scott Marlowe <smarlowe@g2switchworks.com>)
Responses Re: Determining when a row was inserted
Re: Determining when a row was inserted
List pgsql-general

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@g2switchworks.com]
Sent: Friday, June 03, 2005 11:55 AM
To: Wiebe de Jong
Cc: pgsql-general@postgresql.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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Terry Lee Tucker
> Sent: Thursday, June 02, 2005 5:51 AM
> To: pgsql-general@postgresql.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.

pgsql-general by date:

Previous
From: "Gevik babakhani"
Date:
Subject: postgresql books
Next
From: Peter Fein
Date:
Subject: Preventing Multiple Inheritance