很多业务也行有这样的需求，新的数据会不断的插入，并且可能会有更新。
对于更新的数据，需要记录更新前的记录到历史表。
这个需求有点类似于审计需求，即需要对记录变更前后做审计。
我以前有写过使用hstore和触发器来满足审计需求的文档，有兴趣的同学可以参考
http://blog.163.com/digoal@126/blog/static/163877040201252575529358/
本文的目的并不是审计，而且也可能不期望使用触发器。
还有什么方法呢？
PostgreSQL 这么高大上，当然有，而且还能在一句SQL里面完成，看法宝。
创建一张当前状态表，一张历史记录表。
postgres=# create table tbl(id int primary key, price int);
CREATE TABLE
postgres=# create table tbl_history (id int not null, price int);
CREATE TABLE
插入一条不存在的记录，不会触发插入历史表的行为。
注意替代变量
id=$1 = 2
price=$2 = 7
postgres=# with old as (select * from tbl where id= $1),
postgres-# new as (insert into tbl values ($1, $2) on conflict (id) do update set price=excluded.price where tbl.price<>excluded.price returning *)
postgres-# insert into tbl_history select old.* from old,new where old.id=new.id;
INSERT 0 0
postgres=# select tableoid,ctid,* from tbl union all select tableoid,ctid,* from tbl_history ;
tableoid | ctid | id | price
----------+-------+----+-------
18243 | (0,1) | 2 | 7
(1 row)
插入一条不存在的记录，不会触发插入历史表的行为。
id=$1 = 1
price=$2 = 1
postgres=# with old as (select * from tbl where id= $1),
new as (insert into tbl values ($1, $2) on conflict (id) do update set price=excluded.price where tbl.price<>excluded.price returning *)
insert into tbl_history select old.* from old,new where old.id=new.id;
INSERT 0 0
postgres=# select tableoid,ctid,* from tbl union all select tableoid,ctid,* from tbl_history ;
tableoid | ctid | id | price
----------+-------+----+-------
18243 | (0,1) | 2 | 7
18243 | (0,2) | 1 | 1
(2 rows)
插入一条已存在的记录，并且有数据的变更，触发数据插入历史表的行为。
id=$1 = 1
price=$2 = 2
postgres=# with old as (select * from tbl where id= $1),
new as (insert into tbl values ($1, $2) on conflict (id) do update set price=excluded.price where tbl.price<>excluded.price returning *)
insert into tbl_history select old.* from old,new where old.id=new.id;
INSERT 0 1
postgres=# select tableoid,ctid,* from tbl union all select tableoid,ctid,* from tbl_history ;
tableoid | ctid | id | price
----------+-------+----+-------
18243 | (0,1) | 2 | 7
18243 | (0,3) | 1 | 2
18251 | (0,1) | 1 | 1
(3 rows)
插入一条已存在的记录，并且已存在的记录值和老值一样，不会触发将数据插入历史表的行为。
id=$1 = 1
price=$2 = 2
postgres=# with old as (select * from tbl where id= $1),
new as (insert into tbl values ($1, $2) on conflict (id) do update set price=excluded.price where tbl.price<>excluded.price returning *)
insert into tbl_history select old.* from old,new where old.id=new.id;
INSERT 0 0
postgres=# select tableoid,ctid,* from tbl union all select tableoid,ctid,* from tbl_history ;
tableoid | ctid | id | price
----------+-------+----+-------
18243 | (0,1) | 2 | 7
18243 | (0,3) | 1 | 2
18251 | (0,1) | 1 | 1
(3 rows)
执行计划
postgres=# explain with old as (select * from tbl where id= $1),
new as (insert into tbl values ($1, $2) on conflict (id) do update set price=excluded.price where tbl.price<>excluded.price returning *)
insert into tbl_history select old.* from old,new where old.id=new.id;
QUERY PLAN
----------------------------------------------------------------------------
Insert on tbl_history (cost=2.17..2.23 rows=1 width=8)
CTE old
-> Index Scan using tbl_pkey on tbl (cost=0.14..2.16 rows=1 width=8)
Index Cond: (id = 1)
CTE new
-> Insert on tbl tbl_1 (cost=0.00..0.01 rows=1 width=8)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: tbl_pkey
Conflict Filter: (tbl_1.price <> excluded.price)
-> Result (cost=0.00..0.01 rows=1 width=8)
-> Nested Loop (cost=0.00..0.05 rows=1 width=8)
Join Filter: (old.id = new.id)
-> CTE Scan on old (cost=0.00..0.02 rows=1 width=8)
-> CTE Scan on new (cost=0.00..0.02 rows=1 width=4)
(14 rows)
在不支持insert on conflict语法的PostgreSQL中(小于9.5的版本)，SQL可以调整为：
id=$1 = 1
price=$2 = 2
with new as (update tbl set price=$2 where id=$1 and price<>$2)
insert into tbl select $1, $2 where not exists (select 1 from tbl where id=$1);
更多upset参考
https://yq.aliyun.com/articles/36103
小于9.5的版本，实现本文的场景，需要这样写。
id=$1 = 1
price=$2 = 2
with
old as (select * from tbl where id=$1),
new_upd as (update tbl set price=$2 where id=$1 and price<>$2 returning *),
new_ins as (insert into tbl select $1, $2 where not exists (select 1 from tbl where id=$1) returning *)
insert into tbl_history
select old.* from old left outer join new_upd on (old.id=new_upd.id) where new_upd.* is not null;
