Get last modified date of table in postgresql

Question

I want know the last modified date of table in postgresql. In SQL Server can get using

SELECT modify_date FROM sys.objects 

How to get same thing in Postgres? I am using Postgres 9.4

Answer 1

There is a way with Postgres 9.5 or later. Turn on track_commit_timestamp in postgresql.conf and restart the DB cluster. Starts logging commit timestamps.

Then you can get the timestamp of the latest modification (latest commit) for a given table using the function pg_xact_commit_timestamp():

SELECT pg_xact_commit_timestamp(t.xmin) AS modified_ts
FROM   my_table t
ORDER  BY modified_ts DESC NULLS LAST
LIMIT  1;

NULLS LAST is necessary while there still may be rows without recorded commit timestamp.

Related:

• PostgreSQL sort by datetime asc, null first?
• Row with latest timestamp
• Is there way to get transaction commit timestamp in Postgres?

For Postgres 9.4 or older, see:

• How to find out when data was inserted to Postgres?

Answer 2

If the file system stores file modification time (mtime), I believe you can use

SELECT pg_relation_filepath('schema.table');

To find the path to the table's heap. From there you can look up the file modification time (mtime). This has a lot of drawbacks,

1. It may be obscured by WAL, data could be written to the write-ahead log pending a checkpoint. In such a case, nothing has even tried to write it to disk.
2. The FS may be journaling the data itself.
3. The data may be in the write cache of the operating system.

Shy of that you may need to write your own mtime-abilities on the rows being updated/inserted, or the table (by creating a meta-table with its own rows).

CREATE TABLE foo (
  id int,
  mtime timestamp with time zone DEFAULT now()
);
INSERT INTO foo(id) VALUES (1);

or, for a meta table

CREATE TABLE foo (
  id serial,
  fqn_table text,
  mtime timestamp with time zone DEFAULT now()
);