【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 ]
1
2
3
4
5
6

前半部分与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)  
); 
1
2
3
4
5
6
7

创建一张产品信息的表，产品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)

1
2
3
4
5
6
7
8
9
10
11

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
1
2
3
4
5
6
7
8

可以看到，这条插入语句可以执行成功，在发生冲突时，通过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
2
3
4
5
6
7
8
9
10
11

确实，编号为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
1
2
3
4
5
6
7
8

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)

1
2
3
4
5
6
7
8
9
10
11
12

带条件的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
1
2
3
4
5
6
7
8

通过上面查询，实际上发生了冲突，但是条件不满足，所以不会执行冲突动作，此处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)

1
2
3
4
5
6
7
8
9
10
11
12

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

总结

---

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

结尾

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

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

注：未经同意，不得转载！