PostgreSQL 如何实现upsert与新旧数据自动分离-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

PostgreSQL 如何实现upsert与新旧数据自动分离

简介: 很多业务也行有这样的需求,新的数据会不断的插入,并且可能会有更新。对于更新的数据,需要记录更新前的记录到历史表。 这个需求有点类似于审计需求,即需要对记录变更前后做审计。我以前有写过使用hstore和触发器来满足审计需求的文档,有兴趣的同学可以参考http://blog.163.com/digoa

很多业务也行有这样的需求,新的数据会不断的插入,并且可能会有更新。
对于更新的数据,需要记录更新前的记录到历史表。
1
这个需求有点类似于审计需求,即需要对记录变更前后做审计。
我以前有写过使用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;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
最新文章
相关文章
链接