PostgreSQL automatically updates the timestamp when updating tables.

PostgreSQL automatically updates the timestamp when updating tables.

When PostgreSQL updates a table, the timestamp is not automatically updated as follows:

Operating System: CentOS7.3.1611 _ x64

PostgreSQL version: 9.6

Problem description

When PostgreSQL executes the Insert statement, the automatic time filling function can be implemented during table creation, but the timestamp is not automatically updated when the table is updated.

In mysql, you can define automatic update fields when creating a table, for example:

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

How can I operate PostgreSQL?

Solution

Implement the trigger as follows:

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();

Test code:

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 updateset email = excluded.email,num=excluded.num;select * from ts;-- delete from ts;

Okay, that's all. I hope it will help you.

Github address of this article.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.