How to Check When a Table Was Created in PostgreSQL

Short answer: PostgreSQL doesn’t record when a table was created. No system catalog has a created_at column, and nothing in pg_class or pg_stat_user_tables stores it.

That surprises a lot of people, especially if they come from MySQL, which has information_schema.TABLES.CREATE_TIME. In PostgreSQL you have two options:

1. Estimate the creation time for tables that already exist.
2. Start recording creation times for new tables from now on.

This guide covers both, along with the catches that make the estimates wrong.

Why PostgreSQL Doesn’t Store It

When you run CREATE TABLE, PostgreSQL adds a row to pg_class and creates a data file on disk. Neither one includes a timestamp you can rely on. The PostgreSQL developers have talked about adding one several times, but it has never been included.

So any answer about tables that already exist is a clue, not proof.

Method 1: Check the Data File Timestamp (Best Estimate)

Each table is stored as one or more files under the data directory. The file’s timestamps can tell you roughly when it was created.

First, find the file path:

SELECT pg_relation_filepath('public.orders');

 pg_relation_filepath
----------------------
 base/16384/24576

That path is relative to the data directory:

SHOW data_directory;

     data_directory
-----------------------------
 /var/lib/postgresql/16/main

Now check the file on the server:

sudo stat /var/lib/postgresql/16/main/base/16384/24576

  File: /var/lib/postgresql/16/main/base/16384/24576
  Size: 8192        Blocks: 16         IO Block: 4096   regular file
Access: 2026-11-10 09:12:44.103 +0000
Modify: 2026-11-11 02:30:15.882 +0000
Change: 2026-11-11 02:30:15.882 +0000
 Birth: 2026-03-04 14:21:07.519 +0000

The Birth line is the one you want. It shows when the file was created. You need a reasonably recent kernel and coreutils, plus a filesystem that records birth times, such as ext4, XFS, or Btrfs. If you see Birth: -, that information isn’t available. In that case, Modify only tells you when data was last written, which isn’t much help here.

See also: Mastering the Linux Command Line — Your Complete Linux Training Course

Why the File Date Can Be Wrong

This is where most people get caught out. The file belongs to the table’s current storage, not to the table itself. PostgreSQL creates a brand-new file, with a new birth time, whenever these operations run:

• TRUNCATE
• VACUUM FULL
• CLUSTER
• ALTER TABLE commands that rewrite the table, such as changing a column type
• Restoring from pg_dump or pg_restore
• pg_upgrade in copy mode

So the birth time really means “the table was created at or before this time, and its storage hasn’t been rebuilt since.” On a table that gets truncated every night, it will only show last night’s date.

You can check whether a table has been rewritten. If its current file number differs from its OID, the original file has been replaced at least once:

SELECT relname, oid, relfilenode
FROM pg_class
WHERE relname = 'orders';

 relname |  oid  | relfilenode
---------+-------+-------------
 orders  | 24576 |       24576

When oid and relfilenode match, the file is most likely the original. When they differ, the table has been rewritten and the birth time shows the last rewrite.

Method 2: Search the Server Logs

If DDL logging was turned on when the table was created, the log contains the exact time.

Check the current setting:

SHOW log_statement;

If it says ddl or all, search the logs:

sudo grep -i "create table.*orders" /var/log/postgresql/postgresql-16-main.log*

2026-03-04 14:21:07.512 UTC [8812] app_user@shop LOG:  statement: CREATE TABLE orders (...)

That gives you the time, the user, and the database. The catch is that most installations leave log_statement at its default of none, and log files get rotated away. If logging wasn’t on back then, this method won’t help with older tables.

Method 3: Sort Tables by Age with pg_class

If you only need to know which tables are newer than others, not exact dates, pg_class gives you a rough order.

Each table’s OID is assigned when it’s created, and OIDs mostly go up over time:

SELECT relname, oid
FROM pg_class
WHERE relkind = 'r'
  AND relnamespace = 'public'::regnamespace
ORDER BY oid DESC
LIMIT 10;

Tables near the top were most likely created more recently. This ordering is approximate. OIDs are shared with every other object in the cluster, and they can wrap around on very old or very busy clusters.

If track_commit_timestamp = on was already enabled, you can also get a timestamp from the pg_class row:

SELECT relname, pg_xact_commit_timestamp(xmin) AS row_last_changed
FROM pg_class
WHERE relname = 'orders';

This shows when the pg_class row was last changed, not when the table was created. ALTER TABLE, GRANT, COMMENT ON, and similar commands all update that row. If track_commit_timestamp was off at the time, the function returns NULL.

Method 4: Record Creation Times from Now On (Event Trigger)

The only reliable fix is to start recording the information yourself. An event trigger runs after DDL commands and can log each new table to an audit table.

Create the audit table:

CREATE TABLE ddl_audit (
    id              bigserial PRIMARY KEY,
    created_at      timestamptz NOT NULL DEFAULT now(),
    created_by      text        NOT NULL DEFAULT current_user,
    command_tag     text,
    object_identity text
);

Create the function:

CREATE OR REPLACE FUNCTION log_table_creation()
RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE
    r record;
BEGIN
    FOR r IN
        SELECT * FROM pg_event_trigger_ddl_commands()
        WHERE object_type = 'table'
    LOOP
        INSERT INTO ddl_audit (command_tag, object_identity)
        VALUES (r.command_tag, r.object_identity);
    END LOOP;
END;
$$;

Attach it:

CREATE EVENT TRIGGER track_table_creation
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
    EXECUTE FUNCTION log_table_creation();

The WHERE object_type = 'table' filter matters. A CREATE TABLE that includes a serial column or a primary key also creates a sequence and an index, and without the filter those would show up in your audit table too.

Test it:

CREATE TABLE test_audit (id int);

SELECT created_at, created_by, object_identity FROM ddl_audit;

          created_at           | created_by | object_identity
-------------------------------+------------+--------------------
 2026-11-11 10:05:33.218+00    | postgres   | public.test_audit

A few things to know:

• Superuser required: you need superuser rights to create an event trigger.
• One database only: event triggers work per database. Repeat the setup in every database you care about.
• Not retroactive: only tables created after you install the trigger are recorded.

Method 5: pgAudit

If you need a full audit trail for compliance, not just table creation times, use the pgAudit extension. It’s available on most Linux distributions and on managed services like Amazon RDS and Azure Database for PostgreSQL.

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl'

After a restart, every DDL statement goes to the server log with a structured audit prefix. You then search the log as in Method 2, but with better detail and without logging every query.

Which Method Should You Use?

Situation	Method
Rough date for an existing table	File birth time (Method 1)
DDL logging was already on	Search the logs (Method 2)
Just need old vs. new order	OID ordering (Method 3)
Want accurate dates going forward	Event trigger (Method 4)
Need a full audit trail	pgAudit (Method 5)

Summary

PostgreSQL doesn’t store table creation times. For tables that already exist, the data file’s birth time is usually the best clue. Treat it as “no later than,” because operations like TRUNCATE and VACUUM FULL reset it. For new tables, add an event trigger today so you won’t have to guess next time.