浅析PostgreSQL的 ON CONFLICT 和 upsert:不存在则插入/存在则更新、upsert 介绍、语法及示例
一、需求背景
在数据库表里,一般都有主键,主键是不能重复的,因为是唯一标识。假设这个时候需求来了,需要插入一组数据,这些数据中有些是完全新的,可以直接插入(insert),但有些主键内容是和原本表内的数据主键内容是一致的,这些就无法直接插入了,而是执行更新(update)操作。这时候就比较麻烦了,因为如果全部都是通过insert操作,必然会因为发现有重复唯一主键而报错。
一般来说,这时候需要通过业务代码来进行判断:有重复的主键值就执行更新操作,没有就插入操作。但是PostgreSQL就提供了很好的解决方法,语法如下:
-- 1、主键id不重复就插入,否则更新
insert into 表名称 (字段a, 字段b, ...)
values
(value_a, value_b, ...)
on conflict (主键id)
do
update set ...略
-- 2、直接绑定主键名称,主键重复则更新
insert into 表名称 (字段a, 字段b, ...)
values
(value_a, value_b, ...)
on conflict on constraint this_table_key
do
update set ...略
二、PostgreSQL 的 upsert 简介
PostgreSQL 的 upsert 功能:当记录不存在时,执行插入;否则,进行更新。
在关系数据库中,术语 upsert 被称为合并(merge),意思是,当执行 INSERT 操作时,如果数据表中不存在对应的记录,PostgreSQL 执行插入操作;如果数据表中存在对应的记录,则执行更新操作。这就是为什么将其称为 upsert(update or insert)的原因。
通过 INSERT ON CONFLICT 来使用 upsert 功能:
INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;
1、target 可以是:
- (column_name):一个字段名
- ON CONSTRAINT constraint_name:其中的 constraint_name 可以是一个唯一约束的名字
- WHERE predicate:带谓语的 WHERE 子句
2、action 可以是:
- DO NOTHING:当记录存在时,什么都不做
- DO UPDATE SET column_1 = value_1, … WHERE condition:当记录存在时,更新表中的一些字段
注意,ON CONFLICT 只在 PostgreSQL 9.5 以上可用。
三、PostgreSQL 的 upsert 示例
-- 我们新建一个 customers 表来进行演示:
CREATE TABLE customers (
customer_id serial PRIMARY KEY,
name VARCHAR UNIQUE,
email VARCHAR NOT NULL,
active bool NOT NULL DEFAULT TRUE
);
-- customers 表有4个字段:customer_id、name、email 和 active。
-- 其中,name 字段有唯一约束,用于确保客户的唯一性。
-- 下面,往 customers 表里插入几行数据:
#SELECT * FROM customers;
customer_id | name | email | active
-------------+-----------+-----------------------+--------
1 | IBM | contact@ibm.com | t
2 | Microsoft | contact@microsoft.com | t
3 | Intel | contact@intel.com | t
(3 rows)
————————————————
假设 Microsoft 更换了联系方式 email:由 contact@microsoft.com 变成了 hotline@microsoft.com,我们可以使用 UPDATE 语句进行修改。然而,为了演示 upsert 功能,我们使用 INSERT ON CONFLICT 语句
INSERT INTO customers (NAME, email)
VALUES
(
'Microsoft',
'hotline@microsoft.com'
)
ON CONFLICT ON CONSTRAINT customers_name_key
DO NOTHING;
这个语句指明了,当数据存在时,什么都不做(DO NOTING)。下面的语句有一样的效果,区别在于使用的是 name 字段,而不是约束的名字:
INSERT INTO customers (name, email)
VALUES
(
'Microsoft',
'hotline@microsoft.com'
)
ON CONFLICT (name)
DO NOTHING;
我们的目标是修改客户的 email,所以应该用这条语句:
INSERT INTO customers (name, email)
VALUES
(
'Microsoft',
'hotline@microsoft.com'
)
ON CONFLICT (name)
DO
UPDATE
SET email = EXCLUDED.email;
upsert
相关文章
- linux环境,通过rpm删除mysql包,报错:error reading information on service mysqld: Invalid argument
- How to find event listeners on a DOM node in JavaScript or in debugging?
- How can I set up an editor to work with Git on Windows?
- Building Apache Thrift on CentOS 6.5
- $("#SpecialAptitude").on("change",function(){CheckType($(this))})$("#SpecialAptitude").on("change",CheckType($(this)))
- CodeForces 396C On Changing Tree
- qemu运行虚拟机无反应,只输出一行提示信息:VNC server running on 127.0.0.1:5900
- Initialization On Demand Holder idiom的实现探讨
- postgresql 先创建唯一主键 再分区_PostgreSQL 务实应用(三/5)分表复制(转载)
- The last access date is not changed even after reading the file on Windows 7
- TensorFlow on Android:训练模型
- Error writing file '/tmp/MLLGyECY' (Errcode: 28 - No space left on device)
- 问题解决:psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
- Redis_MISCONF Redis is configured to save RDB snapshots, but is currently not able to persist on disk问题解决
- maven No compiler is provided in this environment. Perhaps you are running on a JRE rather than a JDK?
- sqlserver中set IDENTITY_INSERT on 和 off 的设置方法
- The Tomcat connector configured to listen on port 8080 failed to start. The port may already be in use or the connector may be misconfigured