Postgresql 实现数据不存在插入，存在更新

有时候需要往表里增量插入数据，此时可能会出现唯一键重复而没法插入的错误。尤其是一些存储过程中经常出现【无则插入，有则更新】的逻辑。PostgreSQL9.5 开始库提供了Upset方法可以轻松实现此逻辑：

语法形式

INSERT INTO 表名 VALUES ('值１', '值２', ...）
ON CONFLICT ON CONSTRAINT  唯一或排除约束名
DO UPDATE SET 列１='值', 列２='值', ...;

--或
INSERT INTO 表名 VALUES ('值１', '值２', ...）
ON CONFLICT(唯一或排除约束字段名)  
DO UPDATE SET 列１='值', 列２='值', ...;

示例

--若要从person表往TEST表更新全部记录，不重复插入，重复则更新
INSERT INTO TEST
     select * from person 
       on conflict on constraint  pk_test_deviceid_eventtype do 
        update set updatetime = excluded.updatetime, msec = excluded.msec;

实践

create table  test(id int constraint idx_t_id primary key,name varchar(20) constraint cst_name not null);
--插入测试数据
insert into test values(1,'ALMJ');
insert into test values(2,'Tom');
insert into test values(3,'Cat');

创建一个test表，并给id字段设置唯一键约束。看看此表的约束信息

select table_name, constraint_name, constraint_type
from   information_schema.table_constraints
where  table_name='test';
--result
/**
test	idx_t_id	PRIMARY KEY
test	2200_21059_1_not_null	CHECK
test	2200_21059_2_not_null	CHECK
**/

更新一条重复id的记录看

insert into test values(1,'阿力木') ON CONFLICT(id) do update set name=EXCLUDED.name ;
--result
/***
insert into test values(1,'阿力木') ON CONFLICT(id) do update set name=EXCLUDED.name
> Affected rows: 1
> 时间: 0.006s
***/

成功了，或者可能重复的时候不要报错也不要更新，自动跳过

insert into test values(2,'汤姆'),(3,'凯特') ON CONFLICT(id) do nothing ;
--result
/***
insert into test values(2,'汤姆'),(3,'凯特') ON CONFLICT(id) do nothing
> Affected rows: 0
> 时间: 0s
      id      name
       2	Tom
       3	Cat
       1	阿力木
***/