跟踪 Postgres 中一行的最后修改时间戳 [英] Track last modification timestamp of a row in Postgres

问题描述

在 Postgres 中，我想存储表的上次更新/插入时间.Microsoft SQL Server 提供了一种由数据库自动维护的 timestamp 类型.

In Postgres I want to store table's last update/insert time. Microsoft SQL Server offers a type timestamp which is automatically maintained by the database.

但是 timestamp 在 Postgres 中的工作方式不同，它不会自动更新并且该列始终为空.

But timestamp in Postgres works differently, it is not updated automatically and the column is always null.

推荐答案

在 postgresql 中，你必须使用触发器.您可以点击此链接了解如何操作 https://x-team.com/blog/automatic-timestamps-with-postgresql/ .

In postgresql, you have to use a trigger. You can follow this link on how to do it https://x-team.com/blog/automatic-timestamps-with-postgresql/ .

要总结文章，您可以执行以下操作:

To summarize the article you can do the following:

1. 创建将被触发的 Pl/Pgsql 函数:

1. Create the Pl/Pgsql function that will be triggered:

CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建您的表格

Create your table

CREATE TABLE mytable (
  id SERIAL NOT NULL PRIMARY KEY,
  content TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

最后添加触发器:

And finally add the trigger:

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON mytable
FOR EACH ROW
EXECUTE FUNCTION trigger_set_timestamp();

您可以在此处找到有关该问题的更多信息:https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table

You can find more informations about the question here: https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table

希望能帮到你.

这篇关于跟踪 Postgres 中一行的最后修改时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！