PostgreSQL: Creation date of a table

Question

I've recently finished a project, during which many DB tables were created.

Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables.

Is there a way to list all DB tables sorted according to their creation date?

Answer 1

Yes it is possible - with limitations.

See

https://stackoverflow.com/questions/18849756/automatically-drop-tables-and-indexes-older-than-90-days/18852752#18852752

for further information

WITH CTE AS
(
    SELECT 
        table_name 

        ,
        (
            SELECT 
                MAX(pg_ls_dir::int)::text 
            FROM pg_ls_dir('./base') 
            WHERE pg_ls_dir <> 'pgsql_tmp' 
            AND  pg_ls_dir::int  <= (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;

Answer 2

No, that's not possible as far as I know.

That value is not stored in any of the system tables (would be nice though).

Answer 3

This query

select pslo.stasubtype, pc.relname, pslo.statime
from pg_stat_last_operation pslo
join pg_class pc on(pc.relfilenode = pslo.objid)
and pslo.staactionname = 'CREATE'
order by pslo.statime desc 

will help surely.

NB: It works only on greenplum.

Answer 4

Its not possible to get table creation time, but you can actually get the list of tables that are recently created if you vacuum your database regularly. You can use this query: select * from pg_stat_user_tables where last_vacuum > now()-interval '3 days'; you can change the interval as per your need.

Answer 5

The following SQL works much better. It uses the pg_relation_filepath() function. It works for me like a charm in postgresql v12.

WITH cte AS
    (SELECT 
        table_name,
        pg_relation_filepath(table_name::text) AS path
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
    AND table_schema = 'public'
    )
SELECT *, (SELECT creation FROM pg_stat_file(path)) AS creation_time, (SELECT change FROM pg_stat_file(path)) AS change_time
  FROM cte

Note: In my case all my creation times are empty for some reason, while update and change times are ok.