The last data inserted in a Table

[Cristobaal]

Hi Everybory,

How can I know what was the last date that anybody inserted data in a Table ?

Can I see this stadistics ?

Bes Regards!

[NorbertKrupa]

There's no inherent audit trail for data that was loaded in. You would have to manually sift through requests against the table. You should add a column to your table that tracks the time of the change (and perhaps who made the change) if you need this type of audit.

[JimKnicely]

Hi,

I wonder if the EPOCHS system table would help?

Example:

Code: Select all

dbadmin=> SELECT NOW();
              NOW
-------------------------------
 2014-05-19 06:30:20.355969-04
(1 row)

dbadmin=> CREATE TABLE test (c1 INT);
CREATE TABLE
dbadmin=> INSERT INTO test VALUES (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT
dbadmin=> SELECT epoch epoch_number, epoch_close_time "Last DML"
dbadmin->   FROM test
dbadmin->   JOIN epochs
dbadmin->     ON epoch_number = epoch
dbadmin->  ORDER BY 1 DESC;
 epoch_number |           Last DML
--------------+-------------------------------
      1567841 | 2014-05-19 06:30:20.514635-04
(1 row)

[nnani]

Hi Jim,

I believe the epochs table flushes out the data on regular intervals as I cannot find much information or records on older epochs.
So if the data inserted was today for a table surely, we can check what was the last data inserted in table today, but if the data was inserted 3 days back, it hard to join with the epochs table as the epoch table will not be having the epoch number we are looking for.

Is there any other table which keeps a track of epoch on database from beginning.

[JimKnicely]

Maybe you can create your own epochs table and update it every hour or so vis a cron job?

Code: Select all

dbadmin=> create table my_epochs as select epoch_close_time, epoch_number from epochs;
CREATE TABLE
dbadmin=> select max(epoch_close_time), max(epoch_number) from my_epochs;
              max              |  max
-------------------------------+--------
 2014-09-05 15:13:03.144453-04 | 252131
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> insert into my_epochs select epoch_close_time, epoch_number from epochs b where b.epoch_number > (select max(epoch_number) from my_epochs);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select max(epoch_close_time), max(epoch_number) from my_epochs;
              max              |  max
-------------------------------+--------
 2014-09-05 15:13:16.270286-04 | 252132
(1 row)