PostgreSQL， pgsql新增插入数据，存在则更新，不存则在新增插入，ON CONFLICT insert冲突处理

本文主要是介绍PostgreSQL， pgsql新增插入数据，存在则更新，不存则在新增插入，ON CONFLICT insert冲突处理，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

基本语法， 通过 INSERT ON CONFLICT 来使用 upsert 功能

duplicate key value violates unique constraint

[Err] ERROR: duplicate key value violates unique constraint “kxstore_pkey”
DETAIL: Key (id)=(13793638137224) already exists.

upsert ：（update or insert）

INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;

target 可以是：

1、(column_name)：一个字段名
2、ON CONSTRAINT constraint_name：其中的 constraint_name 可以是一个唯一约束的名字
3、WHERE predicate：带谓语的 WHERE 子句

action 可以是：

1、DO NOTHING：当记录存在时，什么都不做
2、DO UPDATE SET column_1 = value_1, … WHERE condition：当记录存在时，更新表中的一些字段

注意，ON CONFLICT 只在 PostgreSQL 9.5 以上可用。

insert into 
table_name (col_1,col_2,col_3,col_4,col_5) 
values (value_1,value_2,value_3,value_4,value_5),
(value1_1,value1_2,value1_3,value1_4,value1_5),.. 
ON CONFLICT(col_1) DO ..

举例

INSERT INTO test_postgre(id,name,InputTime,age)
VALUES('1','postgre','2018-01-10 22:00:00',24)
ON conflict(id) 
DO UPDATE SET name = 'postgreOk', InputTime ='2018-02-22 12:00:00'
-- id存在则更新

INSERT INTO test_postgre(id,name,InputTime,age)
VALUES('1','postgre','2018-01-10 22:00:00',24)
ON conflict(id) 
DO UPDATE SET (name,InputTime,age) = (EXCLUDED.nme,EXCLUDED.InputTime,EXCLUDED.age)
-- id存在则更新

新建一个 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 字段有唯一约束，用于确保客户的唯一性。

upsert

下面，往 customers 表里插入几行：

INSERT INTO customers (NAME, email)
VALUES('IBM', 'contact@ibm.com'),('Microsoft','contact@microsoft.com'),('Intel','contact@intel.com');
#SELECT * FROM customers;
customer_id |   name    |         email         | active
-------------+-----------+-----------------------+--------1 | IBM       | contact@ibm.com       | t2 | Microsoft | contact@microsoft.com | t3 | 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) 
DOUPDATESET email = EXCLUDED.email;
upsert

这篇关于PostgreSQL， pgsql新增插入数据，存在则更新，不存则在新增插入，ON CONFLICT insert冲突处理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---