Python操作PostgreSQL：插入更新时自动维护createdon和updatedon字段

最可靠的方式是在数据库层面通过触发器实现，不需要在Python代码里额外处理，这样不管用什么工具（Python、SQL客户端等）操作表，都能保证时间戳的正确性。

步骤1：创建触发器函数 #

这个函数会在插入或更新行时自动处理时间戳：

CREATE OR REPLACE FUNCTION update_customer_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    -- 插入新行时，让updatedon和createdon保持一致
    IF TG_OP = 'INSERT' THEN
        NEW.updatedon = NEW.createdon;
        RETURN NEW;
    -- 更新行时，仅更新updatedon为当前时间，保留createdon原值
    ELSIF TG_OP = 'UPDATE' THEN
        NEW.updatedon = CURRENT_TIMESTAMP;
        NEW.createdon = OLD.createdon;
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

步骤2：绑定触发器到目标表 #

创建触发器，让上面的函数在customer_data表发生插入或更新时执行：

CREATE TRIGGER customer_data_timestamp_trigger
BEFORE INSERT OR UPDATE ON public.customer_data
FOR EACH ROW EXECUTE FUNCTION update_customer_timestamp();

为什么不推荐在Python中处理？ #

如果非要在Python代码里手动处理，虽然可行，但存在明显缺陷：

• 每次写插入/更新SQL都要手动传入时间戳，容易遗漏或写错；
• 如果有其他渠道（比如直接用SQL客户端）操作表，无法保证时间戳自动更新；
• 代码冗余，增加维护成本。

如果确实要在Python中处理，示例代码（以psycopg2为例）：

import psycopg2
from datetime import datetime

# 获取当前时间戳
current_ts = datetime.now()

# 插入（含冲突更新）的SQL
sql = """
INSERT INTO public.customer_data (sno, fdc_customer_number, gender, createdon, updatedon)
VALUES (%s, %s, %s, %s, %s)
ON CONFLICT (fdc_customer_number) DO UPDATE
SET sno = EXCLUDED.sno, gender = EXCLUDED.gender, updatedon = %s
"""

# 连接数据库并执行
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pwd")
cur = conn.cursor()
cur.execute(sql, (123, 456789, 'M', current_ts, current_ts, current_ts))
conn.commit()
cur.close()
conn.close()

但还是强烈建议用数据库触发器的方案，更稳定通用。

内容的提问来源于stack exchange，提问作者Varun Nagrare