How to find table creation time?

How can I find the table creation time in PostgreSQL?

Example: If I created a file I can find the file creation time like that I want to know the table creation time.

I had a look through the pg_* tables, and I couldn't find any creation times in there. It's possible to locate the table files, but then on Linux you can't get file creation time. So I think the answer is that you can only find this information on Windows, using the following steps:

• get the database id with select datname, datdba from pg_database;
• get the table filenode id with select relname, relfilenode from pg_class;
• find the table file and look up its creation time; I think the location should be something like <PostgreSQL folder>/main/base/<database id>/<table filenode id> (not sure what it is on Windows).

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 https://stackoverflow.com/questions/1137060/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.

--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 to accomplish desired results

(tried it on greenplum)

You can get this from pg_stat_last_operation. Here is how to do it:

select * from pg_stat_last_operation where objid = 'table_name'::regclass order by statime;

This table stores following operations:

select distinct staactionname from pg_stat_last_operation;

 staactionname 
---------------
 ALTER

 ANALYZE

 CREATE
 
 PARTITION
 
 PRIVILEGE
 
 VACUUM
(6 rows)