How to store table creation date in your PostgreSQL database

PostgreSQL lacks a catalog to be able to track the creation date of the DB objects which could be useful for the database administrator for monitoring and maintenance activities. Here's a way to manually create a user-defined function to achieve this.

In the vast world of data management, PostgreSQL stands tall as one of the most popular and powerful open-source relational database management systems. As businesses and organizations rely on PostgreSQL to store and manage their critical data, maintaining accurate and relevant metadata becomes increasingly crucial. Among the essential pieces of information to track is the creation date of database tables, as it plays a significant role in various aspects of database management, data governance, and compliance.

Surprisingly, PostgreSQL lacks a dedicated system catalog table that stores creation dates for its tables by default. However, recognizing the importance of this information, many PostgreSQL administrators and developers have taken the initiative to create custom solutions. In this article, we delve into the significance of storing the creation date of tables in a PostgreSQL database and explore the implementation of a custom table designed explicitly for this purpose.

By harnessing this additional metadata, database administrators and developers can unlock a multitude of benefits, including improved database maintenance, better auditing capabilities, and enhanced data lineage tracking. Join us as we explore the implications of this seemingly simple yet powerful custom table, providing insights into how it can elevate your PostgreSQL database management to new heights.

We have tested this scenario on PostgreSQL version 14.4.0 by creating a trigger to capture the creation timestamp whenever a table is created. We have provided the below steps for the same:-

1. Create a new table to store the creation timestamps:

CREATE TABLE table_creation_dates (
table_name text PRIMARY KEY,
creation_date DATE NOT NULL DEFAULT CURRENT_DATE --(You can also set the creation_date TYPE as 'timestamp default current_timestamp' if you want the time details as well along with the date)
);

2. Create a trigger function that inserts the table name and creation timestamp into the table_creation_dates table:

CREATE OR REPLACE FUNCTION track_table_creation()
RETURNS event_trigger AS
$$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag IN ('CREATE TABLE', 'CREATE FOREIGN TABLE') LOOP
EXECUTE 'INSERT INTO table_creation_dates (table_name) VALUES ($1)' USING obj.object_identity;
END LOOP;
END;
$$
LANGUAGE plpgsql;

3. Enable the event trigger:

CREATE EVENT TRIGGER table_creation_event_trigger
ON ddl_command_end
EXECUTE FUNCTION track_table_creation();

postgres=# \d
List of relations
Schema | Name | Type | Owner 
public | table_creation_dates | table | enterprispostgres
(1 row)


postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type 
public | track_table_creation | event_trigger | | func
(1 row)


postgres=# select * from table_creation_dates ;
table_name | creation_date 
(0 rows)


postgres=# create table table1(i int);
CREATE TABLE


-bash-4.2$ date
Mon Jun 26 04:31:32 PDT 2023


-bash-4.2$ psql postgres
psql (14.4.0, server 14.4.0)
Type "help" for help.

postgres=# select * from table_creation_dates ;
table_name | creation_date 
public.table1 | 26-JUN-23 04:31:27.999964
(1 row)

Note that the above example was tested when the creation_date TYPE as 'timestamp default current_timestamp'

Effectively managing and preserving database metadata is fundamental to the smooth functioning and growth of any organization. Throughout this article, we explored the significance of storing the creation date of tables in a PostgreSQL database and the absence of a built-in mechanism for this purpose. Understanding the importance of this information, we demonstrated the creation of a custom table capable of capturing and storing these crucial timestamps.

By implementing such a solution, PostgreSQL administrators and developers can gain a deeper understanding of their database structure's evolution, enabling them to make informed decisions about maintenance, optimization, and data governance. Furthermore, this custom table empowers organizations to enhance their auditing and compliance capabilities, ensuring data lineage tracking becomes more accessible and reliable.

While PostgreSQL may not offer an out-of-the-box solution for tracking table creation dates, the flexibility of this open-source database management system allows for innovative custom implementations. Embracing the creation of a dedicated table to store such information represents a proactive step towards efficient data management, reinforcing PostgreSQL's position as a powerful and adaptable solution for modern-day database challenges. By leveraging this additional metadata, organizations can unlock the full potential of their PostgreSQL databases and optimize their data-driven decision-making processes.