如何在PostgreSQL中正确处理created_at和updated_at列的插入与更新？

一、手动插入数据时的时间填写 #

直接用PostgreSQL的CURRENT_TIMESTAMP或者now()就能插入当前系统时间，比如插入用户的SQL可以这么写：

INSERT INTO users (name, email, created_at, updated_at)
VALUES ('张三', 'zhangsan@example.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

嫌每次写麻烦的话，还可以给这两个字段加默认值，之后插数据就不用手动填了：

-- 给users表加默认值
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE users ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP;

-- 同理给其他表加
ALTER TABLE posts ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE posts ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE likes ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE likes ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE comments ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE comments ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP;

加完之后，插入数据只需要写业务字段就行：

INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');

二、解决手动更新时updated_at不自动更新的问题 #

Rails是在应用层自动更新updated_at的，但直接在pgAdmin里改数据时，Rails管不到。这时候得给表加数据库触发器，让PostgreSQL自己维护这个字段：

1. 先创建一个通用的触发器函数

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 给每个表绑定触发器

-- users表
CREATE TRIGGER trigger_users_update_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- posts表
CREATE TRIGGER trigger_posts_update_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- likes表
CREATE TRIGGER trigger_likes_update_updated_at
BEFORE UPDATE ON likes
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- comments表
CREATE TRIGGER trigger_comments_update_updated_at
BEFORE UPDATE ON comments
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

这样之后不管是在pgAdmin手动更新数据，还是用Rails代码更新，updated_at都会自动改成当前时间，两边互不冲突。

内容的提问来源于stack exchange，提问作者Waris Haleem