Thread: how do i find out when a record was created/modified?

how do i find out when a record was created/modified?

From
"Andy Kriger"
Date:
if i have a table full of data, is there metadata stored somewhere by psql
that can tell me when each record was created/modified? or do i have to
track this manually with datetime columns that default to now()?

thx
andy



Re: how do i find out when a record was created/modified?

From
Doug McNaught
Date:
"Andy Kriger" <akriger@greaterthanone.com> writes:

> if i have a table full of data, is there metadata stored somewhere by psql
> that can tell me when each record was created/modified? or do i have to
> track this manually with datetime columns that default to now()?

You have to track it manually.

-Doug

Re: how do i find out when a record was created/modified?

From
Oliver Elphick
Date:
On Thu, 2002-11-21 at 23:07, Andy Kriger wrote:
> if i have a table full of data, is there metadata stored somewhere by psql
> that can tell me when each record was created/modified? or do i have to
> track this manually with datetime columns that default to now()?

Defaulting to now() will only track inserts, not updates.

There is a contrib module, spi/moddatetime, that will do both.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Can two walk together, except they be agreed?"
                                     Amos 3:3


Re: how do i find out when a record was created/modified?

From
David Goodenough
Date:
On Friday 22 November 2002 10:12, Oliver Elphick wrote:
> On Thu, 2002-11-21 at 23:07, Andy Kriger wrote:
> > if i have a table full of data, is there metadata stored somewhere by
> > psql that can tell me when each record was created/modified? or do i have
> > to track this manually with datetime columns that default to now()?
>
> Defaulting to now() will only track inserts, not updates.
>
> There is a contrib module, spi/moddatetime, that will do both.

I just went to the web site, and from there to the FTP master and looked
in the contrib directory, and its empty.  Presuming I looked in the wrong
place could you give us a URL please?

David

Re: how do i find out when a record was created/modified?

From
"Nigel J. Andrews"
Date:
On Fri, 22 Nov 2002, David Goodenough wrote:

> On Friday 22 November 2002 10:12, Oliver Elphick wrote:
> > On Thu, 2002-11-21 at 23:07, Andy Kriger wrote:
> > > if i have a table full of data, is there metadata stored somewhere by
> > > psql that can tell me when each record was created/modified? or do i have
> > > to track this manually with datetime columns that default to now()?
> >
> > Defaulting to now() will only track inserts, not updates.
> >
> > There is a contrib module, spi/moddatetime, that will do both.
>
> I just went to the web site, and from there to the FTP master and looked
> in the contrib directory, and its empty.  Presuming I looked in the wrong
> place could you give us a URL please?
>

I don't know if you said what version you are running but it is in the source
tree for 7.2.3. Depending on how and what you have installed you may or may not
already have the binary installed. Try doing 'locate moddatetime' at a shell
prompt.

--
Nigel J. Andrews


Re: how do i find out when a record was created/modified?

From
Oliver Elphick
Date:
On Fri, 2002-11-22 at 11:27, David Goodenough wrote:
> On Friday 22 November 2002 10:12, Oliver Elphick wrote:
> > There is a contrib module, spi/moddatetime, that will do both.
>
> I just went to the web site, and from there to the FTP master and looked
> in the contrib directory, and its empty.  Presuming I looked in the wrong
> place could you give us a URL please?

It's part of the standard issue in postgresql-7.2.3.tar.gz.  I didn't
mean a web page.

If you build from source, look in postgresql-7.2.3/contrib.

If you have a distribution package, look for the contrib package.


--
Oliver Elphick <olly@lfix.co.uk>
LFIX Limited


Re: how do i find out when a record was created/modified?

From
CoL
Date:
And he can use triggers or the moddatetime from contrib.
C.

Doug McNaught wrote, On 11/22/2002 12:31 AM:
> "Andy Kriger" <akriger@greaterthanone.com> writes:
>
>> if i have a table full of data, is there metadata stored somewhere by psql
>> that can tell me when each record was created/modified? or do i have to
>> track this manually with datetime columns that default to now()?
>
> You have to track it manually.
>
> -Doug
>