How to find table creation time?

You can't - the information isn't recorded anywhere. Looking at the table files won't necessarily give you the right information - there are table operations that will create a new file for you, in which case the date would reset.

I don't think it's possible from within PostgreSQL, but you'll probably find it in the underlying table file's creation time.

Suggested here :

SELECT oid FROM pg_database WHERE datname = 'mydb';

Then (assuming the oid is 12345) :

ls -l $PGDATA/base/12345/PG_VERSION

This workaround assumes that PG_VERSION is the least likely to be modified after the creation.

NB : If PGDATA is not defined, check Where does PostgreSQL store the database?

I tried a different approach to get table creation date which could help for keeping track of dynamically created tables. Suppose you have a table inventory in your database where you manage to save the creation date of the tables.

CREATE TABLE inventory (id SERIAL, tablename CHARACTER VARYING (128), created_at DATE);

Then, when a table you want to keep track of is created it's added in your inventory.

CREATE TABLE temp_table_1 (id SERIAL); -- A dynamic table is created
INSERT INTO inventory VALUES (1, 'temp_table_1', '2020-10-07 10:00:00'); -- We add it into the inventory

Then you could get advantage of pg_tables to run something like this to get existing table creation dates:

    SELECT pg_tables.tablename, inventory.created_at
      FROM pg_tables
INNER JOIN inventory
        ON pg_tables.tablename = inventory.tablename

/*
  tablename   | created_at 
--------------+------------
 temp_table_1 | 2020-10-07
*/

For my use-case it is ok because I work with a set of dynamic tables that I need to keep track of.

P.S: Replace inventory in the database with your table name.

1. Check data dir location SHOW data_directory;
2. Check For Postgres relation file path : SELECT pg_relation_filepath('table_name'); you will get the file path of your relation
3. check for creation time of this file <data-dir>/<relation-file-path>

I'm trying to follow a different way for obtain this. Starting from this discussion my solution was:

DROP TABLE IF EXISTS t_create_history CASCADE;
CREATE TABLE t_create_history (
    gid serial primary key,
    object_type varchar(20),
    schema_name varchar(50),
    object_identity varchar(200),
    creation_date timestamp without time zone 
    );



--delete event trigger before dropping function
DROP EVENT TRIGGER IF EXISTS t_create_history_trigger;

--create history function
DROP FUNCTION IF EXISTS public.t_create_history_func();

CREATE OR REPLACE FUNCTION t_create_history_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands  () WHERE command_tag in ('SELECT INTO','CREATE TABLE','CREATE TABLE AS')
    LOOP
        INSERT INTO public.t_create_history (object_type, schema_name, object_identity, creation_date) SELECT obj.object_type, obj.schema_name, obj.object_identity, now();
    END LOOP; 

END;
$$;


--ALTER EVENT TRIGGER t_create_history_trigger DISABLE;
--DROP EVENT TRIGGER t_create_history_trigger;

CREATE EVENT TRIGGER t_create_history_trigger ON ddl_command_end
WHEN TAG IN ('SELECT INTO','CREATE TABLE','CREATE TABLE AS')
EXECUTE PROCEDURE t_create_history_func();

In this way you obtain a table that records all the creation tables.

Based on the other answers, here is a one-liner that will give you the closest to what you are asking. It is not the creation date of the table, only your logs could give you that, but it will tell you the creation time of the baking file if it's available :

select * from
    pg_stat_file(
        (select setting from pg_settings where name = 'data_directory')
        || '/base/'
        || (select oid from pg_database where datname = current_database())
        || '/'
        || (select relfilenode from pg_class where relname = '<table_name>')
    );

Which will give you

  size   |         access         |      modification      |         change         | creation | isdir
---------+------------------------+------------------------+------------------------+----------+-------
 3031040 | 2025-06-18 10:23:41+02 | 2025-06-18 10:23:42+02 | 2025-06-18 10:23:42+02 |          | f
(1 row)

Time: 8.665 ms