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.
Best Answer
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.