patternsqlMinor

PostgreSQL: Creation date of a table

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqlcreationtabledate

Problem

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?

Solution

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#3093, answer score: 8

Revisions (0)

No revisions yet.