当在PostgreSQL中插入新行时，自动填充时间戳字段。

Question

当在PostgreSQL中插入新行时，自动填充时间戳字段。

databasepostgresqltimestampsql-insertdefault-value

229

我希望代码能够在插入新行时自动填充时间戳值，就像在MySQL中使用CURRENT_TIMESTAMP一样。

我该如何在PostgreSQL中实现这个功能？

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

- Aaron

12

顺便提一下，SQL规范中timestamp数据类型的定义是TIMESTAMP WITHOUT TIME ZONE的缩写。根据Postgres专家David E. Wheeler的解释，这几乎肯定不是您想要的类型（http://justatheory.com/computers/databases/postgresql/use-timestamptz.html）。另一种类型`TIMESTAMP WITH TIME ZONE`可能是您想要的，它可以使用任何传递的时区偏移信息来调整日期时间到协调世界时（但实际上并不存储该时区信息，尽管该类型名包含"TIME ZONE"）。 - Basil Bourque

5

在重新学习了这个问题之后，我写了一篇详细的博客文章，介绍如何使用默认值、函数和触发器记录行创建和修改的日期时间。该博客包含完整的SQL和PL/pgSQL代码示例，可在Postgres中使用。 - Basil Bourque

6个回答

191

您需要编写一个插入触发器，如果您希望在记录更改时进行更改，则可能需要编写更新触发器。本文对此进行了很好的解释：

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

- E.J. Brennan

1

这是一个更完整的解决方案，可以与初始的INSERT以及任何后续的UPDATE一起使用（适用于数据审计）。当然，OP只要求前者。 - Pavel Lechev

1

为每个插入创建触发器会使插入过程变得昂贵吗？这能在生产环境中执行吗？ - Aldo aldo

94

仅在值更改时更新时间戳

根据 E.J 的链接，并从此链接 (https://dev59.com/8U7Sa4cB1Zd3GeqP4pAl#3084254) 添加 if 语句。

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';

- Jim Yu

这个问题并不是在询问如何在UPDATE操作中覆盖时间戳。这个问题是在询问如何在执行INSERT查询时将字段值设置为当前时间戳。 - undefined

19

使用“now()”作为默认值，可以自动生成时间戳。

- Parichay Mo

16

仅用于添加价值时，在更新记录时似乎不使用默认值。 - Alex Hope O'Connor

此外，它是一个“默认”值，因此可以被覆盖，这可能会对审计目的造成问题。 - comte

7

为了在PostgreSQL中每次插入新行时自动更新时间戳字段，您可以将current_timestamp设置为其默认值:

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

除此之外，您可能希望防止任何人在将来更新此字段，这可以通过创建更新触发器并应用它来完成：

CREATE OR REPLACE FUNCTION stop_change_on_timestamp()
  RETURNS trigger AS
$BODY$
BEGIN
  -- always reset the timestamp to the old value ("actual creation time")
  NEW.timestamp := OLD.timestamp;
  RETURN NEW;
END;
$BODY$

CREATE TRIGGER prevent_timestamp_changes
  BEFORE UPDATE
  ON users
  FOR EACH ROW
  EXECUTE PROCEDURE stop_change_on_timestamp();

- Ashish Kumar

1

更新和Liquibase YAML:

databaseChangeLog:
  - changeSet:
      id: CREATE FUNCTION set_now_to_timestamp
      author: Konstantin Chvilyov
      changes:
        - sql:
            sql: CREATE OR REPLACE FUNCTION set_now_to_timestamp() RETURNS TRIGGER LANGUAGE plpgsql AS 'BEGIN NEW.timestamp = NOW(); RETURN NEW; END;'

  - changeSet:
      id: CREATE TRIGGER update_users_set_now_to_timestamp
      author: Konstantin Chvilyov
      changes:
        - sql:
            sql: CREATE TRIGGER update_users_set_now_to_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE set_now_to_timestamp();

- RoutesMaps.com

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

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

- user330315 · Accepted Answer

在插入时填充列，请使用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
)

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

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

请注意，使用保留字作为列名通常不是一个好主意。您应该找到一个不同的名称而不是timestamp。