參考文章：【PostgreSQL 如何實現(xiàn)upsert與新舊數(shù)據(jù)自動分離】
背景
很多業(yè)務(wù)也行有這樣的需求复旬，新的數(shù)據(jù)會不斷的插入，并且可能會有更新缰犁。 對于更新的數(shù)據(jù)腺逛，需要記錄更新前的記錄到歷史表。 這個需求有點類似于審計需求纱扭，即需要對記錄變更前后做審計牍帚。 本文的目的并不是審計，而且也可能不期望使用觸發(fā)器跪但。
還有什么方法呢履羞？
PostgreSQL 這么高大上，當然有屡久，而且還能在一句SQL里面完成忆首，看法寶。
騷操作
創(chuàng)建一張當前狀態(tài)表被环，一張歷史記錄表糙及。
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
插入一條不存在的記錄，不會觸發(fā)插入歷史表的行為筛欢。
注意替代變量
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)
插入一條不存在的記錄浸锨，不會觸發(fā)插入歷史表的行為唇聘。
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)
插入一條已存在的記錄，并且有數(shù)據(jù)的變更柱搜，觸發(fā)數(shù)據(jù)插入歷史表的行為迟郎。
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)
插入一條已存在的記錄，并且已存在的記錄值和老值一樣聪蘸，不會觸發(fā)將數(shù)據(jù)插入歷史表的行為宪肖。
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)
執(zhí)行計劃
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)
