PostgreSQL之时间戳自动更新
问题描述
PostgreSQL执行Insert语句时,自动填入时间的功能可以在创建表时实现,但更新表时时间戳不会自动自动更新。
在mysql中可以在创建表时定义自动更新字段,比如 :
create table ab (
id int,
age int,
changetimestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
);
那PostgreSQL中怎么操作呢?
解决方案
1,通过内置的插件包moddatetime实现
create extension moddatetime;
drop table if exists t1;
create table t1 (
id serial not null primary key,
age int,
create_time timestamp(0) without time zone default now()::timestamp(0) without time zone,
update_time timestamp(0) without time zone
);
-- 自动将update_time字段更新为最新时间
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON t1
FOR EACH ROW
EXECUTE PROCEDURE moddatetime (update_time);
--测试
insert into t1 (age) values (1),(2),(3);
update t1 set age=4 where id = 1;
update t1 set age=5 where id = 2;
update t1 set age=6 where id = 3;
select * from t1;
id | age | create_time | update_time
----+-----+---------------------+----------------------------
1 | 4 | 2019-11-05 12:00:50 | 2019-11-05 12:00:50.048446
2 | 5 | 2019-11-05 12:00:50 | 2019-11-05 12:00:50.050179
3 | 6 | 2019-11-05 12:00:50 | 2019-11-05 12:00:50.05179
方案2,触发器实现
drop table if exists t1;
create table t1 (
id serial not null primary key,
age int,
create_time timestamp(0) without time zone default now()::timestamp(0) without time zone,
update_time timestamp(0) without time zone
);
create or replace function update_timestamp() returns trigger as
$$
begin
new.update_time = current_timestamp;
return new;
end
$$
language plpgsql;
create trigger t_update_time before update on t1 for each row execute procedure update_timestamp();
测试
-- 自动将update_time字段更新为最新时间
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON t1
FOR EACH ROW
EXECUTE PROCEDURE moddatetime (update_time);
--测试
insert into t1 (age) values (1),(2),(3);
update t1 set age=4 where id = 1;
update t1 set age=5 where id = 2;
update t1 set age=6 where id = 3;
select * from t1;
id | age | create_time | update_time
----+-----+---------------------+---------------------
1 | 4 | 2019-11-05 12:04:42 | 2019-11-05 12:04:42
2 | 5 | 2019-11-05 12:04:42 | 2019-11-05 12:04:42
3 | 6 | 2019-11-05 12:04:42 | 2019-11-05 12:04:42
(3 rows)
各种数据库迁移到PostgreSQL及PG维保、商业培训、紧急救援及其他服务,请扫码联系。