postgresql get folder creation date

All we need is an easy explanation of the problem, so here it is.

i am trying to get the date when the folder in the filesystem was created using SQL and I cannot find a way to do this. Can someone help me achieve this? This is what i have so far

WITH date_check AS (
    SELECT 
        tablename 

        ,
        (
            select oid from pg_database where datname='PROD'
        ) as dbid 


        ,(SELECT relfilenode FROM pg_class WHERE relname ILIKE tablename) filenode

    FROM PG_TABLES
    WHERE schemaname = 'DUMMY' )

SELECT 
    tablename 
    ,(
        SELECT creation 
        FROM pg_stat_file(
            './base/' || dbid || '/' || filenode 
        )
    ) as creation_time FROM date_check;

My goal is to get the time at which a PostgreSQL table was created.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

There is no way to get the creation timestamp of a table in PostgreSQL. You’ll have to persist that information somewhere (perhaps in a table) when you create a table.

The file creation timestamp does not necessarily reflect the timestamp when the table was created. Operations like VACUUM (FULL), TRUNCATE or certain ALTER TABLE will write a new file for the table. Also, do you want your table creation timestamp to change when the database is restored from backup?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply