Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a means of differentiating rows added after a certain date without explicitly storing that information as a column item? I working with a database that is constantly being filled with information and I'm trying to devise a means of efficiently working with a row subset defined as newly added rows, but I need that date meta information to make said differentiation possible. Is this at all possible?

A dirty solution, that sadly only works on some tables, I've thought up includes keeping track of the number of rows I've collected (this works because the primary key is generated by the database) and then making queries with an offset. If the date added meta information exists then I can devise a generic solution that will suite all of my tables.

Please note that I can't add an explicit data added column because I already have a ton of rows collected over several years.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

The only information you have about 'when' a row entered the table is the ID of the inserting transaction. The sad news is that this is in no way connected to any real timestamp.

So you either add the new column (where you can't figure out when the existing rows were inserted) - or probably you can create triggers on all tables which populate a transaction ID-timestamp table.

The number of rows approach can work only if you keep track of not only the inserts but deletes (and truncates) as well.

Alternatively, if this task is not needed too often, you can use PostgreSQL's point-in-time recovery mechanism to set up a database which mirrors the state of a given time in the past, then compare the data in the two versions.

share|improve this answer
    
Thank you. I appreciate the variety of answers you've given me. Sadly this process will happen quite often so your alternative suggestion, while quite brilliant, will prove too time consuming. I think I'll go the trigger route as a means of checking for new rows. Cheers. –  Mastergeek Jul 17 '13 at 16:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.