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

这篇具有很好参考价值的文章主要介绍了【postgresql 基础入门】UPSERT语句,INSERT违反约束条件时可以转变为UPDATE语句,UPDATE与INSERT的合体。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

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

到了这里,关于【postgresql 基础入门】UPSERT语句,INSERT违反约束条件时可以转变为UPDATE语句,UPDATE与INSERT的合体的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • SpringBoot PostgreSQL upsert

    PostgreSQL 的 upsert 功能:当记录不存在时,执行插入;否则,进行更新。但是版本需要在 9.5 以上 upsert SQL示例 SQL字符串定义 函数定义使用

    2024年02月16日
    浏览(26)
  • mysql批量插入insert语句

    在MySQL中批量插入数据有几种方法,下面我将介绍其中两种常用的方法: 这是一种简单的方式,可以一次性插入多个值。以下是一个示例: 在上面的示例中,你需要替换your_table_name为你的表名,列名和相应的值。你可以一次性插入多行数据。 另一种批量插入数据的方法是使

    2024年02月06日
    浏览(41)
  • Postgresql源码(108)不同类型insert在parse阶段的差异分析

    分析三种类型的insert在parse的各个阶段的差异: 不同insert的计划树type 下面三种SQL在语义分析结果来看有什么区别? 语义分析结果来看,insert语句都会构造插入表和数据表两张表(RangeTblEntry),数据表可能是值构造出来的,或者是select查询出来的。 核心流程都是构造数据表

    2024年02月11日
    浏览(37)
  • mysql中insert语句的五种用法

    insert语句是标准sql中的语法,是插入数据的意思。在实际应用中,它也演变了很多种用法来实现特殊的功能,下面介绍在mysql数据库中insert语句的五种用法。 语法: 如果插入多条数据,需要写多条sql。 语法: 多条数据1条sql即可,相较于方法1效率更高。 语法: 多条数据使用

    2023年04月25日
    浏览(35)
  • INSERT...SELECT语句对查询的表加锁吗

    insert into t2 select * from t1; 这条语句会对查询表 t1 加锁吗?不要轻易下结论。对GreatSQL的锁进行研究之前,首先要确认一下事务的隔离级别,不同的事务隔离级别,锁的表现是不一样的。 创建测试表t1,t2 创建存储过程,向t1表插入测试数据 查询当前事务隔离级别: connection

    2024年03月09日
    浏览(44)
  • 解决生成的insert语句内有单引号的情况

    因为Mybatis-Plus的saveBatch()方法的批量插入其实也是循环插入,而不是真正的一个SqlSession完成的批插,效率很低。所以我们在写批量插入的时候是自己实现了一个工具类去生成批量插入的sql再去执行,但是会遇到有些文本里有单引号导致插入失败的情况,因此需要优化这个工具

    2024年02月04日
    浏览(31)
  • MySQL的insert-on-duplicate语句详解

    注意:ON DUPLICATE KEY UPDATE只是 MySQL的特有语法,并不是SQL标准语法! INSERT INTO … ON DUPLICATE KEY UPDATE 是 MySQL 中一种用于插入数据并处理重复键冲突的语法。 这个语法适用于在 insert的时候,如果insert的数据会引起唯一索引(包括主键索引)的冲突,即唯一值重复了,则不会执行

    2024年04月13日
    浏览(41)
  • 28、Flink 的SQL之DROP 、ALTER 、INSERT 、ANALYZE 语句

    一、Flink 专栏 Flink 专栏系统介绍某一知识点,并辅以具体的示例进行说明。 1、Flink 部署系列 本部分介绍Flink的部署、配置相关基础内容。 2、Flink基础系列 本部分介绍Flink 的基础部分,比如术语、架构、编程模型、编程指南、基本的datastream api用法、四大基石等内容。 3、

    2024年02月06日
    浏览(64)
  • openGauss学习笔记-11 openGauss 简单数据管理-INSERT INTO语句

    在创建一个表后,表中并没有数据,使用这个表之前,需要向表中插入数据。INSERT INTO语句用于向表中插入新记录。该语句可以插入一行数据也可以同时插入多行数据。 11.1 语法格式 11.2 参数说明 table_name 要插入数据的目标表名。 取值范围:已存在的表名。 column_name 目标表中

    2024年02月17日
    浏览(48)
  • 【postgresql 基础入门】数据库服务的管理

    ​ 专栏内容 : postgresql内核源码分析 手写数据库toadb 并发编程 ​ 开源贡献 : toadb开源库 个人主页 :我的主页 管理社区 :开源数据库 座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物. 初始化集群 数据库服务管理 postgresql 数据库是一款通用的关系型数据,在开

    2024年02月10日
    浏览(53)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包