Sidenote/DISCLAIMER:
This is a bad idea, since table creation time this isn't 100% reliable, because the table may have been internally dropped and recreated due to operations on the table, such as CLUSTER.
Apart from that, you can get the creation time like this (assuming example-table-name of t_benutzer):
--select datname, datdba from pg_database;
--select relname, relfilenode from pg_class where relname ilike 't_benutzer';
-- (select relfilenode::text from pg_class where relname ilike 't_benutzer')
SELECT
pg_ls_dir
,
(
SELECT creation
FROM pg_stat_file('./base/'
||
(
SELECT
MAX(pg_ls_dir::bigint)::text
FROM pg_ls_dir('./base')
WHERE pg_ls_dir <> 'pgsql_tmp'
AND pg_ls_dir::bigint <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE 't_benutzer')
)
|| '/' || pg_ls_dir
)
) as createtime
FROM pg_ls_dir(
'./base/' ||
(
SELECT
MAX(pg_ls_dir::bigint)::text
FROM pg_ls_dir('./base')
WHERE pg_ls_dir <> 'pgsql_tmp'
AND pg_ls_dir::bigint <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE 't_benutzer')
)
)
WHERE pg_ls_dir = (SELECT relfilenode::text FROM pg_class WHERE relname ILIKE 't_benutzer')
The secret is to use pg_stat_file on the respective table file.
-- http://www.greenplumdba.com/greenplum-dba-faq/howtofindtablecreationdateingreenplum
select
pg_ls_dir
,
(
select
--size
--access
--modification
--change
creation
--isdir
from pg_stat_file(pg_ls_dir)
) as createtime
from pg_ls_dir('.');
As per comment in this post PostgreSQL: Table creation time
this isn't 100% reliable, because the table may have been internally dropped and recreated due to operations on the table, such as CLUSTER.
Also the pattern
/main/base/<database id>/<table filenode id>
seems to be wrong, as on my machine, all tables from different databases have the same database id, and it seems like the folder has been replaced with some arbitrary inode number, so you need to find the folder whose number is closest to your table's inode id (max foldername where folderid <= table_inode_id and foldername is numeric)
Simplified version goes like this:
SELECT creation
FROM pg_stat_file(
'./base/'
||
(
SELECT
MAX(pg_ls_dir::bigint)::text
FROM pg_ls_dir('./base')
WHERE pg_ls_dir <> 'pgsql_tmp'
AND pg_ls_dir::bigint <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE 't_benutzer')
)
|| '/' || (SELECT relfilenode::text FROM pg_class WHERE relname ILIKE 't_benutzer')
)
Then you can use information_schema and cte to make the query simple, or create your own view:
;WITH CTE AS
(
SELECT
table_name
,
(
SELECT
MAX(pg_ls_dir::bigint)::text
FROM pg_ls_dir('./base')
WHERE pg_ls_dir <> 'pgsql_tmp'
AND pg_ls_dir::bigint <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name)
) as folder
,(SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name) filenode
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = 'public'
)
SELECT
table_name
,(
SELECT creation
FROM pg_stat_file(
'./base/' || folder || '/' || filenode
)
) as creation_time
FROM CTE
(all tables created with nhibernate schema create, so the more or less same time on all tables on the screenshot is correct).
For risk and side effects, use your brain and/or ask your doctor or pharmacist ;)
