upsert插入更新
专栏内容:
- postgresql内核源码分析
- 手写数据库toadb
- 并发编程
个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.
系列文章
- 入门准备
- postgrersql基础架构
- 快速使用
- 初始化集群
- 数据库服务管理
- psql客户端使用
- pgAdmin图形化客户端
- 数据库的使用
- 创建数据库
- 数据库操作
- 表的使用
- 表的创建
- 表的操作
- 数据查询
- 数据查询
- 多表联合查询
- 数据操作
- 插入数据的方式
前言
postgresql 数据库是一款通用的关系型数据,在开源数据库中能与商业数据媲美,在业界也越来越流行。
因为是开源数据库,不仅公开源码,还有很多使用案例,好用的插件,所以它的慢慢变成了数据库的先驱和标准,通过postgresql可以很好从使用到原理,彻底搞懂;
如果是学习编程,也可以学到丰富的编程知识,数据结构,编程技巧,它里面还有很多精妙的架构设计,分层思想,可以灵活定制的思想。
本专栏主要介绍postgresql 入门使用,数据库维护管理,通过这些使用来了解数据库原理,慢慢了解postgresql是什么样的数据库,能做那些事情,以及如何做好服务,最关键的是这些知识都是面试的必备项。
概述
在我们插入数据时,尤其是批量插入数据,经常会碰到某一条数据已经存在,插入失败的情况,不得不停下来进行检查,看看需要更新呢,还是什么都不做,这使得加载数据的任务变得很麻烦。
本文将给大家分享insert语句针对此种情况的处理,这就是upsert方式,也就是把update,insert 能同时处理,就会避免上述的麻烦。
语法介绍
upsert 的基本语法句式如下:
INSERT INTO table_name [( column_name [, ...] ) ]
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column_name)
DO NOTHING
| DO UPDATE SET { column_name = ..., column_name = ...,...}
[ WHERE condition ]
前半部分与insert语法SQL相同,还是insert into ... values ...
这个结构, on conflict
关键字来定义冲突的列名,一般能够产生冲突的列,都是主键,外键,约束检查,或者有索引的列,它们要保持唯一值,或者是其它约束条件;
upsert语法执行流程:
- 首先,在
on conflict
指定的列上有违反约束的情况发生时,就产生了冲突; - 然后,由
DO
关键字决定冲突时的执行动作,有两种行为可选:
do nothing
什么都不做,也就是保持已有数据,不再新插入,当然在批量插入时就不会报错停下来;do update
这个方法就是执行update操作,将旧数据修改为新插入的数据;当然这里还可以带有条件过滤;可以通过excluded
关键字来引用待插入的列值,而不带此关键字的列名表示已存在的列值;
案例分析
下面和大家一起来对几个案例进行练习和分析,首先准备一些数据。
-- 创建产品表
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
category VARCHAR(255)
);
创建一张产品信息的表,产品ID是主键,默认具有唯一性约束,不能存在重复值,再插入一些数据。
postgres=# select * from products ;
product_id | product_name | price | category
------------+--------------+--------+----------
1 | pen | 9.90 | type1
2 | shirt | 202.40 | type2
3 | cake | 37.80 | type4
4 | pencil | 11.40 | type1
5 | hat | 88.40 | type2
6 | milk | 19.80 | type4
(6 rows)
upsert简单SQL
当我们新增库存时,按新的编号插入产品,此时库中有此编号已经存在,就会违反主键的唯一性约束;
postgres=# INSERT INTO products (product_id, product_name, price, category)
VALUES (1, 'iphone', 8999.01, 'type5')
ON CONFLICT(product_id)
DO UPDATE SET
product_name = EXCLUDED.product_name,
price = EXCLUDED.price,
category = EXCLUDED.category;
INSERT 0 1
可以看到,这条插入语句可以执行成功,在发生冲突时,通过excluded
引用待插入的值更新数据,然后查看一下库中的数据变化。
postgres=# select * from products ;
product_id | product_name | price | category
------------+--------------+---------+----------
2 | shirt | 202.40 | type2
3 | cake | 37.80 | type4
4 | pencil | 11.40 | type1
5 | hat | 88.40 | type2
6 | milk | 19.80 | type4
1 | iphone | 8999.01 | type5
(6 rows)
确实,编号为1的产品名称,价格,类型都发生了变化,更新为插入的值。但这里有个有趣的现象,原来全表查询时,编号为1的产品排在第一行,而这次查询时,它居然排在了最后一行,这是为什么呢?
哎,这个超纲了,有兴趣的朋友可以查看我其它关于postgresql的博客,会找到答案的。
插入数据
当不发生冲突时,upsert就是一条普通的insert语句。
postgres=# INSERT INTO products (product_id, product_name, price, category)
VALUES (7, 'keyboard', 92.01, 'type5')
ON CONFLICT(product_id)
DO UPDATE SET
product_name = EXCLUDED.product_name,
price = EXCLUDED.price,
category = EXCLUDED.category;
INSERT 0 1
insert语句执行成功了,再次查询,可以看到编号为7的产品信息已经添加到库里了。
postgres=# select * from products ;
product_id | product_name | price | category
------------+--------------+---------+----------
2 | shirt | 202.40 | type2
3 | cake | 37.80 | type4
4 | pencil | 11.40 | type1
5 | hat | 88.40 | type2
6 | milk | 19.80 | type4
1 | iphone | 8999.01 | type5
7 | keyboard | 92.01 | type5
(7 rows)
带条件的upsert
在冲突发生时,为了数据的正确性,有必要对当前数据进行一个有效性检查,符合条件时再执行冲突行为,不符合条件时什么都不做。
我们再插入一条产品信息,同时产品类型必须与插入的类型相同,此时发生冲突就会进行update,不冲突时就会insert一条产品信息。
postgres=# INSERT INTO products (product_id, product_name, price, category)
VALUES (5, 'egg', 3.89, 'type4')
ON CONFLICT(product_id)
DO UPDATE SET
product_name = EXCLUDED.product_name,
price = EXCLUDED.price
WHERE products.category = 'type4';
INSERT 0 0
通过上面查询,实际上发生了冲突,但是条件不满足,所以不会执行冲突动作,此处where条件中需要增加表名来指定引用的是原表数据,还是待插入的临时表的数据,因为两个表的列名相同,不指定表名时会产生二义性。
postgres=# select * from products ;
product_id | product_name | price | category
------------+--------------+---------+----------
2 | shirt | 202.40 | type2
3 | cake | 37.80 | type4
4 | pencil | 11.40 | type1
5 | hat | 88.40 | type2
6 | milk | 19.80 | type4
1 | iphone | 8999.01 | type5
7 | keyboard | 92.01 | type5
(7 rows)
再次查询验证,表中的数据没有发生变化。
总结
通过对UPSERT语句的介绍,当我们导入数据时,对于违反约束条件的数据,我们可以指定它的冲突时的行为,是不插入,还是执行更新操作,当然也可以指定更精确的过滤条件。
结尾
非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!
作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。文章来源:https://www.toymoban.com/news/detail-838898.html
注:未经同意,不得转载!文章来源地址https://www.toymoban.com/news/detail-838898.html
到了这里,关于【postgresql 基础入门】UPSERT语句,INSERT违反约束条件时可以转变为UPDATE语句,UPDATE与INSERT的合体的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!