Postgres中timestamptz字段使用CURRENT_TIMESTAMP/NOW()报错排查

错误根源 #

你的错误不是触发器函数里的NEW.updated_at := NOW()导致的，问题出在触发触发器的UPDATE语句本身：

从你提供的执行查询可以看到，应用层传入的JSON数据里，created_at和updated_at的值是"Invalid Date"——这是一个无法被PostgreSQL解析为timestamptz类型的无效字符串。当json_populate_record尝试把这个JSON转换为public.documents类型的记录时，直接抛出了类型转换错误，此时触发器函数还没开始执行。

解决方案 #

1. 修复数据来源：检查前端或应用层代码，确保传入的created_at和updated_at是合法的时间格式（比如ISO8601标准格式，如"2022-10-09T01:44:28.100Z"）。
2. 避免手动更新自动维护字段：如果updated_at是由触发器自动维护的字段，在UPDATE语句里完全可以去掉对它的赋值，让触发器来处理；created_at通常是记录创建时设置的，更新操作不应该修改这个字段，也可以从UPDATE的字段列表中移除。
3. 可选：添加数据校验：在应用层或数据库表层面添加约束，确保只有合法的时间值能被写入created_at和updated_at字段。

补充说明 #

你的触发器函数逻辑本身没有问题，NOW()和CURRENT_TIMESTAMP都能正确返回timestamptz类型值，只要触发它的UPDATE语句能正常生成合法的NEW记录，触发器就能正常工作。

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