postgresql upsert 使用范例

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: --pg 9.5 版本支持 "UPSERT" 特性, 这个特性支持 INSERT 语句定义 ON CONFLICT DO UPDATE/IGNORE 属性,当插入 SQL 违反约束的情况下定...
--pg 9.5 版本支持 "UPSERT" 特性, 这个特性支持 INSERT 语句定义 ON CONFLICT DO UPDATE/IGNORE 属性,当插入 SQL 违反约束的情况下定义动作,而不抛出错误

--创建测试数据表
create table t (id int constraint idx_t_id primary key,name varchar(20) constraint cst_name not null);
insert into t values(1,'rudy');
postgres=# select * from t;
 id | name 
----+------
  1 | rudy
(1 row)

--根据字段,当id冲突时更新name值
postgres=# insert into t values(1,'rudy1') ON CONFLICT(id) do update set name=EXCLUDED.name ;
INSERT 0 1
postgres=# select * from t;
 id | name  
----+-------
  1 | rudy1
  

--也可以直接指定约束名,此时不需要字段,在实际应用中,最好使用字段名
postgres=# insert into t values(2,'rudy3') ON CONFLICT ON CONSTRAINT idx_t_id do update set name=EXCLUDED.name ; 
INSERT 0 1
postgres=# select * from t;
 id | name  
----+-------
  1 | rudy1
  2 | rudy3
(2 rows)


--根据where条件选择性更新,由于id没有大于10的数据,故更新0条数据                                                                ^
postgres=# insert into t values(2,'rudy4') ON CONFLICT ON CONSTRAINT idx_t_id do update set name=EXCLUDED.name where t.id>10 ;
INSERT 0 0
postgres=# select * from t;
 id | name  
----+-------
  1 | rudy1
  2 | rudy3
 
--只插入满足条件的数据行 
postgres=# insert into t values(2,'rudy4'),(3,'rudy3') ON CONFLICT(id) do nothing ; 
INSERT 0 1
postgres=# select * from t;
 id | name  
----+-------
  1 | rudy1
  2 | rudy3
  3 | rudy3

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
SQL 弹性计算 关系型数据库
PostgreSQL 大宽表,全列索引,高并发合并写入(insert into on conflict, upsert, merge insert) - 实时adhoc query
标签 PostgreSQL , 全列索引 , 大宽表 , 写测试 , insert on conflict , upsert , merge insert , adhoc query 背景 OLAP系统中,adhoc query非常场景(任意维度查询分析)。 adhoc query,通常来说,可以加GIN倒排,或者每一列都加一个索引来实现。 《PostgreSQL 设计优化case
8446 0
|
弹性计算 关系型数据库 测试技术
PostgreSQL 分区表如何支持多列唯一约束 - 枚举、hash哈希 分区, 多列唯一, insert into on conflict, update, upsert, merge insert
标签 PostgreSQL , 分区表 , native partition , 唯一 , 非分区键唯一 , 组合唯一 , insert into on conflict , upsert , merge insert 背景 PG 11开始支持HASH分区,10的分区如果要支持hash分区,可以通过枚举绕道实现。 《PostgreSQL 9.x, 10, 11 hash分区表 用法举例
2932 0
|
关系型数据库 PostgreSQL
PostgreSQL merge insert(upsert/insert into on conflict) 如何区分数据是INSERT还是UPDATE
标签 PostgreSQL , merge insert , upsert , insert into on conflict , 区分 insert update , xmin , xmax 背景 使用insert into on conflict update语法,可以支持UPSERT的功能,但是到底这条SQL是插入的还是更新的呢?如何判断 通过xmax字段的值是否不为0,可以判断,如果是UPDATE,XMAX里面会填充更新事务号。
2064 0
|
关系型数据库 PostgreSQL 测试技术
|
关系型数据库 PostgreSQL
PostgreSQL 如何实现upsert与新旧数据自动分离
很多业务也行有这样的需求,新的数据会不断的插入,并且可能会有更新。对于更新的数据,需要记录更新前的记录到历史表。 这个需求有点类似于审计需求,即需要对记录变更前后做审计。我以前有写过使用hstore和触发器来满足审计需求的文档,有兴趣的同学可以参考http://blog.163.com/digoa
6776 0
|
关系型数据库 PostgreSQL SQL
postgresql 9.5版本之前实现upsert功能
最近有开发人员问,有没有办法实现在pg9.5版本之前实现upsert功能,现整理如下 --创建测试表,注意此处先不要主键或唯一约束 create table t2 (id int,name varchar(100)); -- pg 在9.
1297 0
|
7天前
|
SQL 关系型数据库 数据库
关系型数据库选择合适的数据库管理系统
【5月更文挑战第5天】关系型数据库选择合适的数据库管理系统
259 2
关系型数据库选择合适的数据库管理系统