How to find table creation time?

Question

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.

Crowbar

Answer 1

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

Localize

There are some operations on a table, such as CLUSTER, that will generate a new file and not re-use the old one. So this is not a reliable method. – Horsefly

@Alex Korban: Fully automatized this here: #18850256 – Brimstone

@Quandary: Interesting, thanks. Looks like there's still no bulletproof method to do it, other than storing creation times yourself. – Localize

I need to be a master user? Can you express a are a query (that works), like the @Manoj's? – Gambia

Have a look at @Quandary's link: #18850256 – Localize

Great answer. Something I have been looking around for sometime. Further, SELECT oid from pg_database WHERE datname = ‘<database_name>’ will give the database folder name within the base folder that holds all the databases – Randolf

For future searchers, it is possible to determine an upper bound of the table creation time, which may be sufficient for some use cases. (That is, you can say, "The table was created no later than the creation timestamp of the table file.") – Sentinel

Answer 2

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.

Horsefly

Do you know which table operations create a new file? Is that renaming tables? Or adding fields? Knowing the history of the table and which operations create a new file I could at least guestimate the creation time. – Trying

Answer 3

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

Marion

Answer 4

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?

Halftone

Answer 5

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.

Idolah

relation "inventory" does not exist LINE 3: INNER JOIN inventory – Christenachristendom

@PrasadLele it's not supposed to work with copy&paste. As I mentioned, "Suppose you have a table inventory in your database"... If you don't have a table inventory created it will obviously fail. You need to replace inventory in the query with your table name. I would appreciate if you remove your negative vote having clarified this. – Tradesman

I've replaced inventory to my table name but it doesn't work anyway. – Teirtza

Can you give more information about the error @DennisV.R. ? – Tradesman

@JaumeJiménez example with my PostgreSQL 9.6 pastebin.com/raw/ckus87RX – Teirtza

@DennisV.R. I may explained poorly the case. The use case for this solution needs a table inventory which keeps track of the tables when they are created, so it's a table with columns (id, tablename, created_at). The snippet was not meant to be a copy & paste, it has to fit in your use case. I' have edited the answer so I hope it can help you now. – Tradesman

Answer 6

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>

Potentiate

Answer 7

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.

Conchoidal

Answer 8

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

Marketplace

This is the same answer as Manoj's and greenplum specific. – Inchmeal

I am using two tables – Marketplace

Answer 9

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)

Emasculate

ERROR: "pg_stat_last_operation not exist", I am using pg9.2.4. – Gambia

pg_stat_last_operation is Greenplum. – Monney

Table pg_stat_all_tables could also help. – Cathryncathy