Argthtjtr

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 Where does PostgreSQL store the database?

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.

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)

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