PostgreSQL更新表时时间戳不会自动更新的解决方法

create table ab (

  id int ,

  changetimestamp timestamp

   NOT NULL

   default CURRENT_TIMESTAMP

   on update CURRENT_TIMESTAMP

);

create or replace function upd_timestamp() returns trigger as

$$

begin

   new.modified = current_timestamp ;

   return new;

end

$$

language plpgsql;

drop table if exists ts;

create table ts (

   id   bigserial primary key ,

   tradeid integer ,

   email varchar (50),

   num integer ,

   modified timestamp default current_timestamp

);

create trigger t_name before update on ts for each row execute procedure upd_timestamp();

insert into ts (tradeid,email,num) values (1223, 'mike_zhang@live.com' ,1);

update ts set email= 'Mike_Zhang@live' where tradeid = 1223 ;

create unique index ts_tradeid_idx on ts(tradeid);

insert into ts(tradeid,email,num) values (1223, 'Mike_Zhang@live.com' ,2) on conflict(tradeid) do update

set email = excluded.email,num=excluded.num;

select * from ts;

-- delete from ts;