PostgreSQL：表创建时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2577168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Table creation time
提问by kiruthika
How can I find the table creation time in Postgresql.
如何在 Postgresql 中找到表创建时间。
Example ,
例子 ，
If I created a file I can find the file creation time like that I want to know the table creation time.
如果我创建了一个文件，我可以找到文件创建时间，就像我想知道表创建时间一样。
采纳答案by Alex Korban
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:
我查看了 pg_* 表，但在那里找不到任何创建时间。可以找到表文件，但是在 Linux 上您无法获得文件创建时间。所以我认为答案是你只能在 Windows 上找到这些信息，使用以下步骤：
- 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).
- 获取数据库 ID
select datname, datdba from pg_database; - 获取表文件节点 ID
select relname, relfilenode from pg_class; - 找到表文件并查找其创建时间；我认为该位置应该类似于
<PostgreSQL folder>/main/base/<database id>/<table filenode id>（不确定它在 Windows 上是什么）。
回答by Magnus Hagander
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.
你不能 - 信息没有记录在任何地方。查看表格文件不一定会为您提供正确的信息 - 有表格操作将为您创建一个新文件，在这种情况下日期将重置。
回答by Marcelo Cantos
I don't think it's possible from within PostgreSQL, but you'll probably find it in the underlying table file's creation time.
我认为在 PostgreSQL 内部是不可能的，但是您可能会在底层表文件的创建时间中找到它。
回答by Skippy le Grand Gourou
建议在这里？：
SELECT oid FROM pg_database WHERE datname = 'mydb';
Then (assuming the oidis 12345)?:
然后（假设oid是 12345）？：
ls -l $PGDATA/base/12345/PG_VERSION
This workaround assumes that PG_VERSIONis the least likely to be modified after the creation.
此变通方法假定PG_VERSION在创建后最不可能被修改。
NB?: If PGDATAis not defined, check Where does PostgreSQL store the database?
注意？：如果PGDATA没有定义，请检查PostgreSQL存储数据库的位置？
回答by Sergio Gollino
I'm trying to follow a different way for obtain this. Starting from this discussionmy 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.
这样你就得到了一个记录所有创建表的表。
回答by Jaume Jiménez
I tried a different approach to get table creation date. Suppose you have a table inventoryin your database where you manage to save the creation date of the tables. Then you could run something like this to get existing table creation dates:
我尝试了一种不同的方法来获取表创建日期。假设您inventory的数据库中有一个表，您可以在其中保存表的创建日期。然后你可以运行这样的东西来获取现有的表创建日期：
SELECT pg_tables.tablename, inventory.created_at
FROM pg_tables
INNER JOIN inventory
ON pg_tables.tablename = inventory.tablename
For my use-case it is ok because I work with a set of dynamic tables that I need to keep track of.
对于我的用例，这是可以的，因为我使用一组需要跟踪的动态表。
Edit: Replace inventoryin the database with your table name.
编辑：inventory在数据库中替换为您的表名。
回答by Manoj
You can get this from pg_stat_last_operation. Here is how to do it:
你可以从 pg_stat_last_operation 得到这个。这是如何做到的：
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)
回答by Gurupreet Singh Bhatia
--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)
（在greenplum上试过）
