database 如何自动更新 PostgreSQL 中的时间戳

How do I automatically update a timestamp in PostgreSQL

提问by Aaron

I want the code to be able to automatically update the time stamp when a new row is inserted as I can do in MySQL using CURRENT_TIMESTAMP.

我希望代码能够在插入新行时自动更新时间戳，就像我在 MySQL 中使用 CURRENT_TIMESTAMP 所做的那样。

How will I be able to achieve this in PostgreSQL?

我将如何在 PostgreSQL 中实现这一目标？

CREATE TABLE users (
    id serial not null,
    firstname varchar(100),
    middlename varchar(100),
    lastname varchar(100),
    email varchar(200),
    timestamp timestamp
)

回答by a_horse_with_no_name

To populate the column during insert, use a DEFAULTvalue:

要在插入期间填充列，请使用一个DEFAULT值：

CREATE TABLE users (
  id serial not null,
  firstname varchar(100),
  middlename varchar(100),
  lastname varchar(100),
  email varchar(200),
  timestamp timestamp default current_timestamp
)

Note that the value for that column can explicitly be overwritten by supplying a value in the INSERTstatement. If you want to prevent that you do need a trigger.

请注意，可以通过在INSERT语句中提供值来显式覆盖该列的值。如果你想防止你确实需要一个trigger。

You also need a trigger if you need to update that column whenever the row is updated (as mentioned by E.J. Brennan)

如果您需要在更新行时更新该列，则还需要一个触发器（如EJ Brennan 所述）

Note that using reserved words for column names is usually not a good idea. You should find a different name than timestamp

请注意，对列名使用保留字通常不是一个好主意。你应该找到一个不同的名字timestamp

回答by E.J. Brennan

You'll need to write an insert trigger, and possible an update trigger if you want it to change when the record is changed. This article explains it quite nicely:

您需要编写一个插入触发器，如果​​您希望它在记录更改时更改，则可能需要编写一个更新触发器。这篇文章很好地解释了它：

http://www.revsys.com/blog/2006/aug/04/automatically-updating-a-timestamp-column-in-postgresql/

http://www.revsys.com/blog/2006/aug/04/automatically-updating-a-timestamp-column-in-postgresql/

CREATE OR REPLACE FUNCTION update_modified_column()   
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;   
END;
$$ language 'plpgsql';

Apply the trigger like this:

CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON customer FOR EACH ROW EXECUTE PROCEDURE  update_modified_column();

CREATE OR REPLACE FUNCTION update_modified_column()   
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;   
END;
$$ language 'plpgsql';

像这样应用触发器：

CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON customer FOR EACH ROW EXECUTE PROCEDURE  update_modified_column();

回答by Jim Yu

Updating timestamp, only if the values changed

更新时间戳，仅当值更改时

Based on E.J's link and add a if statement from this link (https://stackoverflow.com/a/3084254/1526023)

基于 EJ 的链接并从此链接添加 if 语句（https://stackoverflow.com/a/3084254/1526023）

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.modified = now(); 
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF;
END;
$$ language 'plpgsql';

回答by Parichay Mo

Using 'now()' as default value automatically generates time-stamp.

使用 'now()' 作为默认值会自动生成时间戳。