如何自动更新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屋！