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

postgresql


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

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?

Solution 4:[4]

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.

Solution 5:[5]

  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>

Solution 6:[6]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Magnus Hagander
Solution 2 Marcelo Cantos
Solution 3
Solution 4
Solution 5 Oshank Kumar
Solution 6 Sergio Gollino

Related Questions