PostgreSQL之时间戳自动更新

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维保、商业培训、紧急救援及其他服务，请扫码联系。