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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 很多业务也行有这样的需求,新的数据会不断的插入,并且可能会有更新。对于更新的数据,需要记录更新前的记录到历史表。 这个需求有点类似于审计需求,即需要对记录变更前后做审计。我以前有写过使用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;
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
6天前
|
关系型数据库 数据库
关系型数据库的数据完整性
关系型数据库通过一系列机制和技术手段来确保数据的完整性,从而为用户提供准确、一致和可靠的数据服务。这些措施对于保障数据的质量、满足用户需求以及维护系统的稳定运行具有重要意义。
22 5
|
6天前
|
SQL Oracle 关系型数据库
关系型数据库根据某些条件修改数据
SQL的`UPDATE`语句用于根据条件修改关系型数据库(如MySQL、PostgreSQL、Oracle)中的数据。基本语法是:`UPDATE 表名 SET 列名=新值 WHERE 条件`。例如,要将`students`表中名字为&quot;John Doe&quot;的学生年龄改为25,可以使用`UPDATE students SET age = 25 WHERE name = &#39;John Doe&#39;`。执行`UPDATE`前需备份数据,先在测试环境验证,并考虑事务处理以确保数据安全。注意优化表结构和使用索引来提升性能。
23 3
|
6天前
|
关系型数据库 数据库 数据安全/隐私保护
关系型数据库的数据完整性保障
【5月更文挑战第9天】关系型数据库的数据完整性保障
9 1
|
1天前
|
存储 关系型数据库 分布式数据库
【PolarDB开源】深入PolarDB内核:探究存储计算分离架构的设计哲学
【5月更文挑战第20天】PolarDB是阿里巴巴的云原生分布式数据库,以其存储计算分离架构为核心,解决了传统数据库的扩展性和资源灵活性问题。该架构将数据存储和计算处理分开,实现高性能(通过RDMA加速数据传输)、高可用性(多副本冗余保证数据可靠性)和灵活扩展(计算资源独立扩展)。通过动态添加计算节点以应对业务流量变化,PolarDB展示了其在云时代应对复杂业务场景的能力。随着开源项目的进展,PolarDB将持续推动数据库技术发展。
28 6
|
1天前
|
存储 监控 前端开发
关系型数据库数据输入验证
【5月更文挑战第12天】
34 5
|
1天前
|
存储 关系型数据库 数据库
关系型数据库的数据完整性约束
【5月更文挑战第12天】关系型数据库的数据完整性约束
11 2
|
4天前
|
存储 SQL Oracle
关系型数据库文件方式存储DATA FILE(数据文件)
【5月更文挑战第11天】关系型数据库文件方式存储DATA FILE(数据文件)
15 3
|
5天前
|
监控 安全 关系型数据库
关系型数据库数据完整性保障
【5月更文挑战第10天】关系型数据库数据完整性保障
16 4
|
6天前
|
关系型数据库 MySQL 分布式数据库
如何恢复PolarDB数据到特定时间点?
【5月更文挑战第13天】如何恢复PolarDB数据到特定时间点?
9 0
|
6天前
|
关系型数据库 MySQL 分布式数据库
如何备份PolarDB数据?
【5月更文挑战第13天】如何备份PolarDB数据?
8 0

相关产品

  • 云原生数据库 PolarDB