PostgreSQL: Table creation time

I had a look through the pg_* tables, and I couldn't find any creation times in there. It's possible to locate the table files, but then on Linux you can't get file creation time. So I think the answer is that you can only find this information on Windows, using the following steps:

• get the database id with select datname, datdba from pg_database;
• get the table filenode id with select relname, relfilenode from pg_class;
• find the table file and look up its creation time; I think the location should be something like <PostgreSQL folder>/main/base/<database id>/<table filenode id> (not sure what it is on Windows).

You can't - the information isn't recorded anywhere. Looking at the table files won't necessarily give you the right information - there are table operations that will create a new file for you, in which case the date would reset.

I don't think it's possible from within PostgreSQL, but you'll probably find it in the underlying table file's creation time.

Suggested here :

SELECT oid FROM pg_database WHERE datname = 'mydb';

Then (assuming the oid is 12345) :

ls -l $PGDATA/base/12345/PG_VERSION

This workaround assumes that PG_VERSION is the least likely to be modified after the creation.

NB : If PGDATA is not defined, check Where does PostgreSQL store the database?