Get last modified date of table in postgresql

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:

For Postgres 9.4 or older, see:

Share

Improve this answerFollow

edited Aug 23, 2020 at 23:05

answered Apr 19, 2019 at 0:55

Erwin Brandstetter's user avatar

Erwin Brandstetter

156k1919 gold badges384384 silver badges527

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s