How to get the Creation Date of a Table

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article describes how to get the creation date of a table.

 

Environment

Resolution

Follow the steps to find the creation date of a table:

1. Get the OID of the table from pg_class

template1=# select oid,* from pg_class where relname='test';
 oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relaosegrelid | relaosegidxid
 | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass
 | relfrozenxid | relacl | reloptions
-------+---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+--------------
-+-------------+-------------+---------+------------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+---------------
-+--------------+--------+------------
 42187 | test | 2200 | 42188 | 10 | 0 | 42187 | 0 | 2 | 1002 | 42224 | 0 | 0 | 0
 | f | f | r | h | 2 | 0 | 0 | 0 | 0 | 0 | f | f | f | f
 | 1123863 | |
(1 row)

2. Get the creation date from pg_stat_last_operation

template1=# select * from pg_stat_last_operation where objid=42187;
 classid | objid | staactionname | stasysid | stausename | stasubtype | statime
---------+-------+---------------+----------+------------+------------+-------------------------------
 1259 | 42187 | CREATE | 10 | gpadmin | TABLE | 2016-10-13 04:28:06.213131+08
 1259 | 42187 | ANALYZE | 10 | gpadmin | | 2016-10-13 04:32:13.200262+08
 1259 | 42187 | ALTER | 10 | gpadmin | ADD COLUMN | 2016-10-27 01:48:19.849112+08
 1259 | 42187 | VACUUM | 10 | gpadmin | | 2016-11-09 00:30:54.177818+08
(4 rows)

Additional Information

Here's the action which will be recorded in pg_stat_last_operation:

template1=# select distinct staactionname from pg_stat_last_operation;
staactionname
---------------
ALTER
PRIVILEGE
ANALYZE
PARTITION
CREATE
VACUUM

 

 

+ Environment:

• Pivotal Greenplum (GPDB) 4.3.x
• Operating System- Red Hat Enterprise Linux 6.x