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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There is a way with Postgres 9.5 or later. Turn on
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
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.
For Postgres 9.4 or older, see:
If the file system stores file modification time (mtime), I believe you can use
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,
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() );