PostgreSQL中对比初始表与更新表，配置modifiedDate字段触发函数

1. 先确保表包含modifiedDate字段 #

如果你的表还没有modifiedDate字段，先执行下面的语句添加：

ALTER TABLE your_table_name
ADD COLUMN modifiedDate TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;

• 如果希望字段不允许为空，可以加上NOT NULL：

ALTER TABLE your_table_name
ADD COLUMN modifiedDate TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP;

2. 创建触发函数 #

这个函数会在表行更新时自动更新modifiedDate，同时可以通过内置变量对比更新前后的数据：

CREATE OR REPLACE FUNCTION update_modified_date()
RETURNS TRIGGER AS $$
BEGIN
    -- 可选：对比更新前后的特定字段，按需添加逻辑
    -- 比如判断某个字段是否发生变化
    -- IF OLD.some_column <> NEW.some_column THEN
    --     -- 这里可以添加日志记录或其他自定义操作
    -- END IF;

    -- 强制将modifiedDate设为当前时间
    NEW.modifiedDate = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• 函数里的OLD是更新前的行数据快照，NEW是更新后的行数据快照，你可以通过这两个变量对比任意字段的变化，比如限制只有特定字段修改时才更新modifiedDate，或者把变化内容记录到日志表。

3. 绑定触发器到目标表 #

创建触发器，让上面的函数在表执行UPDATE操作时触发：

CREATE TRIGGER trigger_update_modified_date
BEFORE UPDATE ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION update_modified_date();

• 这个触发器会在每一行更新前触发函数，确保modifiedDate被同步为最新时间。

验证效果 #

执行一条更新语句测试：

UPDATE your_table_name SET some_column = 'new_value' WHERE id = 1;

然后查询表查看结果：

SELECT modifiedDate FROM your_table_name WHERE id = 1;

你会看到modifiedDate已经更新为当前时间。

关于对比初始表与更新后表的补充说明 #

触发函数中的OLD和NEW就是你需要的"初始行"和"更新后行"的直接引用。比如你想只在特定字段变化时更新modifiedDate，可以修改函数：

CREATE OR REPLACE FUNCTION update_modified_date_on_specific_change()
RETURNS TRIGGER AS $$
BEGIN
    -- 只有当name字段变化时，才更新modifiedDate
    IF OLD.name <> NEW.name THEN
        NEW.modifiedDate = CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

内容的提问来源于stack exchange，提问作者Hridayesh Manandhar