如何查找表的创建时间？

Question

如何查找表的创建时间？

postgresql

60

我该如何在PostgreSQL中查找表格的创建时间？

举例说明：如果我创建了一个文件，我可以像这样查找到文件的创建时间，现在我想知道表格的创建时间。

- kiruthika

9个回答

25

您无法查看记录的日期信息。直接查看表格文件并不能保证获取正确的信息，因为有些操作会创建新文件，这种情况下日期会重置。

- Magnus Hagander

1

你知道哪些表操作会创建新文件吗？是重命名表还是添加字段？了解表的历史记录以及哪些操作会创建新文件，至少可以估计出创建时间。 - kramer65

3

我认为从PostgreSQL内部并不可能实现，但你可能会在底层表文件的创建时间中找到它。

- Marcelo Cantos

2

这里建议：

SELECT oid FROM pg_database WHERE datname = 'mydb';

假设 oid 是 12345，那么：

ls -l $PGDATA/base/12345/PG_VERSION

这个解决方法假设在创建后，PG_VERSION 最不可能被修改。

注意：如果未定义 PGDATA，请查看PostgreSQL 数据库存储在哪里？。

- Skippy le Grand Gourou

1

检查数据目录位置展示 data_directory;
检查Postgres关系文件路径：选择pg_relation_filepath('table_name'); 您将获得您的关系文件路径
检查此文件的创建时间 <data-dir>/<relation-file-path>

- Oshank Kumar

0

我尝试了一种不同的方法来获取表格创建日期，这可以帮助跟踪动态创建的表格。假设您在数据库中有一个名为inventory的表格，您可以在其中保存表格的创建日期。

CREATE TABLE inventory (id SERIAL, tablename CHARACTER VARYING (128), created_at DATE);

然后，当你想要跟踪的表被创建时，它会被添加到你的库存中。

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

然后你可以利用pg_tables来运行类似这样的代码，以获取现有表的创建日期：

    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
*/

对于我的用例来说，这是可以的，因为我使用一组需要跟踪的动态表。

附言：在数据库中将inventory替换为您的表名。

- Jaume Jiménez

关系“inventory”不存在第3行：INNER JOIN inventory - Prasad Lele

1

@PrasadLele 这个功能不支持复制和粘贴。正如我所提到的，“假设您在数据库中有一个名为inventory的表”... 如果您没有创建名为inventory的表，它显然会失败。您需要在查询中将inventory替换为您的表名称。如果您已经明确了这一点，请撤销您的负面投票，我将不胜感激。 - Jaume Jiménez

我已经将库存替换为我的表名，但它仍然无法工作。 - Dennis V

你能提供更多关于 @DennisV.R. 错误的信息吗？ - Jaume Jiménez

@JaumeJiménez 的 PostgreSQL 9.6 示例 https://pastebin.com/raw/ckus87RX - Dennis V

@DennisV.R. 我可能没有很好地解释这个案例。这个解决方案的使用情况需要一个名为“inventory”的表，用于跟踪表格何时被创建，因此它是一个具有列（id、tablename、created_at）的表。这段代码片段不是要复制和粘贴，而是要适应您的使用情况。我已经编辑了答案，希望现在能对您有所帮助。 - Jaume Jiménez

-1

我正在尝试用另一种方式来获取这个。从这个讨论开始，我的解决方案是：

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

以此方式，您将获得一个记录所有创建表的表格。

- Sergio Gollino

-4

--查询

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

将有助于实现所需的结果

（在Greenplum上尝试过）

- Gurupreet Singh Bhatia

这与Manoj的答案相同，且仅适用于Greenplum。 - dezso

我正在使用两个表。 - Gurupreet Singh Bhatia

-5

您可以从pg_stat_last_operation中获取此信息。以下是如何操作的步骤：

select * from pg_stat_last_operation where objid = 'table_name'::regclass order by statime;

这个表存储以下操作：

select distinct staactionname from pg_stat_last_operation;

 staactionname 
---------------
 ALTER

 ANALYZE

 CREATE

 PARTITION

 PRIVILEGE

 VACUUM
(6 rows)

- Manoj

6

错误: "pg_stat_last_operation not exist"，我正在使用 pg9.2.4。该错误提示指出在使用 PostgreSQL 9.2.4 版本时，查询中使用了不存在的 "pg_stat_last_operation" 函数。 - Peter Krauss

4

pg_stat_last_operation是Greenplum中的一个功能。 - Peter Eisentraut

2

pg_stat_all_tables 表也可能有所帮助。 - Hans Ginzel

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alex Korban · Accepted Answer

我查看了pg_*表，但没有找到其中的创建时间。虽然可以找到表文件，但在Linux上却无法获取文件的创建时间。因此，我认为只有在Windows上，才能通过以下步骤找到这些信息：

使用select datname, datdba from pg_database;获得数据库ID；
使用select relname, relfilenode from pg_class;获得表文件节点ID；
找到表文件并查看其创建时间。我认为位置应该是类似于<PostgreSQL folder>/main/base/<database id>/<table filenode id>（不确定在Windows上是什么）。