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

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
)

4 个解决方案

#1

123

To populate the column during insert, use a DEFAULT value:

要在插入期间填充列，请使用默认值:

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 INSERT statement. If you want to prevent that you do need a trigger.

注意，可以通过在INSERT语句中提供值显式地覆盖该列的值。如果你想防止这种情况发生，你需要一个触发器。

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

如果需要在行更新时更新该列，还需要一个触发器(如E.J. Brennan所述)

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

注意，为列名使用保留词通常不是一个好主意。您应该找到与时间戳不同的名称

#2

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/

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

#3

Updating timestamp, only if the values changed

只在值更改时更新时间戳

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

基于E。J的链接并从该链接中添加if语句(https://*.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';

#4

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

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

#1

123