PostgreSQL之时间戳自动更新
问题描述
PostgreSQL执行Insert语句时，自动填入时间的功能可以在创建表时实现，但更新表时时间戳不会自动自动更新。
在mysql中可以在创建表时定义自动更新字段，比如：
createtableab(
idint,
ageint,
changetimestamptimestampNOTNULLdefaultCURRENT_TIMESTAMPonupdateCURRENT_TIMESTAMP
);
那PostgreSQL中怎么操作呢？
解决方案
1,通过内置的插件包moddatetime实现
createextensionmoddatetime;
droptableifexistst1;
createtablet1(
idserialnotnullprimarykey,
ageint,
create_timetimestamp(0)withouttimezonedefaultnow()::timestamp(0)withouttimezone,
update_timetimestamp(0)withouttimezone
);
--自动将update_time字段更新为最新时间
CREATETRIGGERupdate_timestamp
BEFOREUPDATEONt1
FOREACHROW
EXECUTEPROCEDUREmoddatetime(update_time);
--测试
insertintot1(age)values(1),(2),(3);
updatet1setage=4whereid=1;
updatet1setage=5whereid=2;
updatet1setage=6whereid=3;
select*fromt1;
id|age|create_time|update_time
----+-----+---------------------+----------------------------
1|4|-11-0512:00:50|-11-0512:00:50.048446
2|5|-11-0512:00:50|-11-0512:00:50.050179
3|6|-11-0512:00:50|-11-0512:00:50.05179
方案2，触发器实现
droptableifexistst1;
createtablet1(
idserialnotnullprimarykey,
ageint,
create_timetimestamp(0)withouttimezonedefaultnow()::timestamp(0)withouttimezone,
update_timetimestamp(0)withouttimezone
);
createorreplacefunctionupdate_timestamp()returnstriggeras
$$
begin
new.update_time=current_timestamp;
returnnew;
end
$$
languageplpgsql;
createtriggert_update_timebeforeupdateont1foreachrowexecuteprocedureupdate_timestamp();
测试
--自动将update_time字段更新为最新时间
CREATETRIGGERupdate_timestamp
BEFOREUPDATEONt1
FOREACHROW
EXECUTEPROCEDUREmoddatetime(update_time);
--测试
insertintot1(age)values(1),(2),(3);
updatet1setage=4whereid=1;
updatet1setage=5whereid=2;
updatet1setage=6whereid=3;
select*fromt1;
id|age|create_time|update_time
----+-----+---------------------+---------------------
1|4|-11-0512:04:42|-11-0512:04:42
2|5|-11-0512:04:42|-11-0512:04:42
3|6|-11-0512:04:42|-11-0512:04:42
(3rows)
各种数据库迁移到PostgreSQL及PG维保、商业培训、紧急救援及其他服务，请扫码联系。
