【postgresql 基础入门】UPSERT语句,INSERT违反约束条件时可以转变为UPDATE语句,UPDATE与INSERT的合体

本文主要是介绍【postgresql 基础入门】UPSERT语句,INSERT违反约束条件时可以转变为UPDATE语句,UPDATE与INSERT的合体,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

upsert插入更新

专栏内容

  • postgresql内核源码分析
  • 手写数据库toadb
  • 并发编程

个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.

系列文章

  • 入门准备
  • postgrersql基础架构
  • 快速使用
  • 初始化集群
  • 数据库服务管理
  • psql客户端使用
  • pgAdmin图形化客户端
  • 数据库的使用
  • 创建数据库
  • 数据库操作
  • 表的使用
  • 表的创建
  • 表的操作
  • 数据查询
  • 数据查询
  • 多表联合查询
  • 数据操作
  • 插入数据的方式

文章目录

  • upsert插入更新
  • 系列文章
  • 前言
  • 概述
  • 语法介绍
  • 案例分析
    • upsert简单SQL
    • 插入数据
    • 带条件的upsert
  • 总结
  • 结尾

前言


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 | type12 | shirt        | 202.40 | type23 | cake         |  37.80 | type44 | pencil       |  11.40 | type15 | hat          |  88.40 | type26 | 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 SETproduct_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 | type23 | cake         |   37.80 | type44 | pencil       |   11.40 | type15 | hat          |   88.40 | type26 | milk         |   19.80 | type41 | 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 SETproduct_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 | type23 | cake         |   37.80 | type44 | pencil       |   11.40 | type15 | hat          |   88.40 | type26 | milk         |   19.80 | type41 | iphone       | 8999.01 | type57 | 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 SETproduct_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 | type23 | cake         |   37.80 | type44 | pencil       |   11.40 | type15 | hat          |   88.40 | type26 | milk         |   19.80 | type41 | iphone       | 8999.01 | type57 | keyboard     |   92.01 | type5
(7 rows)

再次查询验证,表中的数据没有发生变化。

总结


通过对UPSERT语句的介绍,当我们导入数据时,对于违反约束条件的数据,我们可以指定它的冲突时的行为,是不插入,还是执行更新操作,当然也可以指定更精确的过滤条件。

结尾

非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!

作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。

注:未经同意,不得转载!

这篇关于【postgresql 基础入门】UPSERT语句,INSERT违反约束条件时可以转变为UPDATE语句,UPDATE与INSERT的合体的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/789512

相关文章

Spring Security 从入门到进阶系列教程

Spring Security 入门系列 《保护 Web 应用的安全》 《Spring-Security-入门(一):登录与退出》 《Spring-Security-入门(二):基于数据库验证》 《Spring-Security-入门(三):密码加密》 《Spring-Security-入门(四):自定义-Filter》 《Spring-Security-入门(五):在 Sprin

零基础学习Redis(10) -- zset类型命令使用

zset是有序集合,内部除了存储元素外,还会存储一个score,存储在zset中的元素会按照score的大小升序排列,不同元素的score可以重复,score相同的元素会按照元素的字典序排列。 1. zset常用命令 1.1 zadd  zadd key [NX | XX] [GT | LT]   [CH] [INCR] score member [score member ...]

数论入门整理(updating)

一、gcd lcm 基础中的基础,一般用来处理计算第一步什么的,分数化简之类。 LL gcd(LL a, LL b) { return b ? gcd(b, a % b) : a; } <pre name="code" class="cpp">LL lcm(LL a, LL b){LL c = gcd(a, b);return a / c * b;} 例题:

Java 创建图形用户界面(GUI)入门指南(Swing库 JFrame 类)概述

概述 基本概念 Java Swing 的架构 Java Swing 是一个为 Java 设计的 GUI 工具包,是 JAVA 基础类的一部分,基于 Java AWT 构建,提供了一系列轻量级、可定制的图形用户界面(GUI)组件。 与 AWT 相比,Swing 提供了许多比 AWT 更好的屏幕显示元素,更加灵活和可定制,具有更好的跨平台性能。 组件和容器 Java Swing 提供了许多

【IPV6从入门到起飞】5-1 IPV6+Home Assistant(搭建基本环境)

【IPV6从入门到起飞】5-1 IPV6+Home Assistant #搭建基本环境 1 背景2 docker下载 hass3 创建容器4 浏览器访问 hass5 手机APP远程访问hass6 更多玩法 1 背景 既然电脑可以IPV6入站,手机流量可以访问IPV6网络的服务,为什么不在电脑搭建Home Assistant(hass),来控制你的设备呢?@智能家居 @万物互联

poj 2104 and hdu 2665 划分树模板入门题

题意: 给一个数组n(1e5)个数,给一个范围(fr, to, k),求这个范围中第k大的数。 解析: 划分树入门。 bing神的模板。 坑爹的地方是把-l 看成了-1........ 一直re。 代码: poj 2104: #include <iostream>#include <cstdio>#include <cstdlib>#include <al

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就

【Linux 从基础到进阶】Ansible自动化运维工具使用

Ansible自动化运维工具使用 Ansible 是一款开源的自动化运维工具,采用无代理架构(agentless),基于 SSH 连接进行管理,具有简单易用、灵活强大、可扩展性高等特点。它广泛用于服务器管理、应用部署、配置管理等任务。本文将介绍 Ansible 的安装、基本使用方法及一些实际运维场景中的应用,旨在帮助运维人员快速上手并熟练运用 Ansible。 1. Ansible的核心概念

AI基础 L9 Local Search II 局部搜索

Local Beam search 对于当前的所有k个状态,生成它们的所有可能后继状态。 检查生成的后继状态中是否有任何状态是解决方案。 如果所有后继状态都不是解决方案,则从所有后继状态中选择k个最佳状态。 当达到预设的迭代次数或满足某个终止条件时,算法停止。 — Choose k successors randomly, biased towards good ones — Close

音视频入门基础:WAV专题(10)——FFmpeg源码中计算WAV音频文件每个packet的pts、dts的实现

一、引言 从文章《音视频入门基础:WAV专题(6)——通过FFprobe显示WAV音频文件每个数据包的信息》中我们可以知道,通过FFprobe命令可以打印WAV音频文件每个packet(也称为数据包或多媒体包)的信息,这些信息包含该packet的pts、dts: 打印出来的“pts”实际是AVPacket结构体中的成员变量pts,是以AVStream->time_base为单位的显