https://dba.stackexchange.com/questions/199290/get-last-modified-date-of-table-in-postgresql

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?

Share

Improve this answerFollow

edited Aug 23, 2020 at 23:05

answered Apr 19, 2019 at 0:55

Erwin Brandstetter

156k1919 gold badges384384 silver badges527