Postgresql 更新的当前时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2362871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql Current timestamp on Update
提问by Mithun Sreedharan
What is the postgres equivalent of the below mysql code
以下 mysql 代码的 postgres 等价物是什么
CREATE TABLE t1 (
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE t2 (
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
As per Alex Brasetvik answer below, it seems i should go with triggers, my problem is i have a number of tables t1, t2... with created and modified fields, is it possible to write a generalized procedure?
根据下面的 Alex Brasetvik 回答，似乎我应该使用触发器，我的问题是我有许多表 t1、t2... 带有创建和修改的字段，是否可以编写通用程序？
--update Almost ready
--update 差不多准备好了
CREATE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$
BEGIN
NEW.modified := current_timestamp;
RETURN NEW;
END;
$update_timestamp$ LANGUAGE plpgsql;
CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON t1
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON t2
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
采纳答案by Frank Heikens
Just make sure all tables have the same columnname:
只需确保所有表具有相同的列名：
CREATE OR REPLACE FUNCTION upd_timestamp() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
NEW.modified = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$;
CREATE TRIGGER t_name
BEFORE UPDATE
ON tablename
FOR EACH ROW
EXECUTE PROCEDURE upd_timestamp();
回答by Lauri Silvennoinen
Thank you for the information Mithun and Alex Brasetvik.
感谢 Mithun 和 Alex Brasetvik 提供的信息。
I'd like to add one minor tweak to the trigger. Since we mostly likely want the modifiedcolumn to store the timestamp when the row was last changed, not when it was the target of an UPDATE statement, we have to compare the new and the old value of the row. We update the modifiedcolumn only if these two values differ.
我想为触发器添加一个小调整。由于我们很可能希望修改后的列存储该行上次更改时的时间戳，而不是当它是 UPDATE 语句的目标时，我们必须比较该行的新值和旧值。只有当这两个值不同时，我们才更新修改后的列。
CREATE OR REPLACE FUNCTION update_modified_timestamp() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
IF (NEW != OLD) THEN
NEW.modified = CURRENT_TIMESTAMP;
RETURN NEW;
END IF;
RETURN OLD;
END;
$$;
This trigger ensures that the modified column is updated only if the UPDATE operation actually changes the values stored in the row.
此触发器确保仅当 UPDATE 操作实际更改存储在行中的值时才更新修改后的列。
回答by Alex Brasetvik
Update it with a trigger. Documentation and examples.
用触发器更新它。文档和示例。
