有时候需要往表里增量插入数据,此时可能会出现唯一键重复而没法插入的错误。尤其是一些存储过程中经常出现【无则插入,有则更新】的逻辑。PostgreSQL9.5 开始库提供了Upset方法可以轻松实现此逻辑:
INSERT INTO 表名 VALUES ('值1', '值2', ...) ON CONFLICT ON CONSTRAINT 唯一或排除约束名 DO UPDATE SET 列1='值', 列2='值', ...; --或 INSERT INTO 表名 VALUES ('值1', '值2', ...) ON CONFLICT(唯一或排除约束字段名) DO UPDATE SET 列1='值', 列2='值', ...;
--若要从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 阿力木 ***/