Solution to the time stamps not automatically updated when PostgreSQL updates tables

The solution to the time stamps will not be automatically updated when PostgreSQL updates the table. The details are as follows

Operating system: CentOS7.3.1611_x64

PostgreSQL version: 9.6

Problem description

When PostgreSQL executes an Insert statement, the function of automatically filling in time can be implemented when creating a table, but the timestamp will not be automatically updated when updating the table.

In mysql, you can define automatic update fields when creating tables, such as:

create table ab (
 id int,
 changetimestamp timestamp
  NOT NULL
  default CURRENT_TIMESTAMP
  on update CURRENT_TIMESTAMP
);

So how to operate in PostgreSQL?

Solution

Implemented through triggers, as follows:

create or replace function upd_timestamp() returns trigger as
$$
begin
   = 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();

Test code:

insert into ts (tradeid,email,num) values (1223,'mike_zhang@',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@',2) on conflict(tradeid) do update
set email = ,num=;

select * from ts;
-- delete from ts;

OK, that's all, I hope it will be helpful to you.

This articlegithub address。

The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.