7

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

3
  • 1
    That's not possible. Postgres does not maintain that information
    – user1822
    Mar 3, 2018 at 10:35
  • 1
    no other way to get information then Mar 3, 2018 at 10:37
  • To clarify—because I was looking for same!—MSSQL sys.objects.modify_date is timestamp from last ALTER(create) performed on the table. That is, it's the last time there was a change to table structure. I consider this a substantial deficiency. I will check for a bug report on this. Jun 17, 2019 at 15:28

2 Answers 2

8

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:

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()
);
1
  • i cant create mtime. Its not in our schema Mar 5, 2018 at 6:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.