Thread: how do i find out when a record was created/modified?
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
"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
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
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
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
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
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 >