9.5 版本支持 “UPSERT” 特性，这个特性支持 INSERT 语句定义 ON CONFLICT DO UPDATE/IGNORE 属性，当插入 SQL 违反约束的情况下定义动作，而不抛出错误，这个特性之前有不少开发人员咨询过。

环境准备

定义一张用户登陆表

fdb=> create table user_logins (username character varying (64) primary key,logins numeric(10,0));  
CREATE TABLE  
  
fdb=> insert into user_logins values ('francs',1);  
INSERT 0  1  
  
fdb=> insert into user_logins values ('matiler',2);  
INSERT 0  1  
  
fdb=>  select  *  from user_logins ;  
username | logins   
----------+--------  
francs | 1  
matiler | 2

增加两个用户登陆信息: 违反主键约束

1
2
3

fdb=> INSERT INTO user_logins (username, logins) VALUES ('tutu',1),('francs',1);  
ERROR:duplicate key value violates unique constraint "user_logins_pkey"  
DETAIL: Key  (username)=(francs) already exists.

定义 ON CONFLICT 属性

INSERT INTO user_logins (username, logins)  
VALUES ('tutu',1),('francs',1)  
ON CONFLICT (username)  
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;  
  
fdb=>  select  *  from user_logins ;  
username | logins   
----------+--------  
matiler | 2  
tutu | 1  
francs | 2

备注：定义 ON CONFLICT 属性后，已有的用户只需更新 logins 值。EXCLUDED 为试图插入的值。

ON CONFLICT 语法

[ ON CONFLICT [ conflict_target ] conflict_action ]

备注：conflict_target 指冲突类型，例如 unique 约束或用户自定义约束。conflict_action 定义当冲突发生时采取的动作，例如 ON CONFLICT DO NOTHING 和 ON CONFLICT DO UPDATE。

再来看个例子：一张 city 表

fdb=> create table citys(city_name character varying(64) primary key );  
CREATE TABLE  
  
fdb=> insert into citys values('Hanzhoug'),('beijing'),('shanghai');  
INSERT 0  3  
  
fdb=>  select  *  from citys ;  
city_name   
-----------  
Hanzhoug  
beijing  
shanghai  
(3 rows)

再插入两条数据：违反唯一约束

1
2
3

fdb=> insert into citys values('Hanzhoug'),('shenzhen');  
ERROR:duplicate key value violates unique constraint "citys_pkey"  
DETAIL: Key  (city_name)=(Hanzhoug) already exists.

定义 On conflict do nothing 属性

fdb=> insert into citys values('Hanzhoug'),('shenzhen') on conflict do nothing;  
INSERT 0  1  
  
fdb=>  select  *  from citys ;  
city_name   
-----------  
Hanzhoug  
beijing  
shanghai  
shenzhen  
(4 rows)

备注：可见没有违反主键约束的数据可以插入，违反了主键约束的数据不能插入，但也不报错，这在一些日志表的使用场很有用，当然上面这个例子并没有指定 conflict_target ，假如表上有多个约束，则应该指定 conflict_target。

指定 conflict_target

1 2	fdb=> insert into citys values('Hanzhoug'),('suzhou') on conflict (city_name) do nothing; INSERT 0 1

ON CONFLICT 属性也能指定 WHERE

1 2	INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') ON CONFLICT (did) WHERE is_active DO NOTHING;

参考

INSERT COMMAND

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/239630.html

PostgreSQL9.5：INSERT ON CONFLICT UPDATE, otherwise known as “UPSERT”

环境准备

定义 ON CONFLICT 属性

定义 On conflict do nothing 属性

参考

发表回复

PostgreSQL9.5：INSERT ON CONFLICT UPDATE, otherwise known as “UPSERT”

环境准备

定义 ON CONFLICT 属性

定义 On conflict do nothing 属性

参考

相关推荐

发表回复