I am trying to find out the date that each table was created in the database. I used the following code:
SELECT relfilenode,
relname, (pg_stat_file('./base/24576' || '/' || relfilenode::text)).*
FROM pg_class
WHERE relkind LIKE 'r'
AND relfilenode <> 0
order by modification ASC;
The result has null for the creation date of all tables:
How can I have the correct creation date for the tables??
As per the documentation, the creation date column only applies to Windows platforms.
Besides, that's not a very reliable way to get the creation date of a table. When you perform a VACUUM FULL on a table, the relfilenode will change (as well as a few other maintenance commands)--when that happens, you would be led to think that the "creation date" was "just now." If you want to track schema changes, you should implement event triggers.
Disclosure: I work for EnterpriseDB (EDB).
