Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 down vote accepted

Found this on Stackoverflow:

share|improve this answer

Yes it is possible - with limitations.


for further information


            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 creation 
        FROM pg_stat_file(
            './base/' || folder || '/' || filenode 
    ) as creation_time
share|improve this answer

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).

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.