如何在 PostgreSQL 中自动更新时间戳 [英] How do I automatically update a timestamp in PostgreSQL

问题描述

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

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.

如何在 PostgreSQL 中实现这一点?

How will I be able to achieve this in PostgreSQL?

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

推荐答案

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

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
)

请注意，可以通过在 INSERT 语句.如果你想防止你确实需要一个 触发器.

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.

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

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

请注意，对列名使用保留字通常不是一个好主意.您应该找到与 timestamp

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

这篇关于如何在 PostgreSQL 中自动更新时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！