PostgreSQL 中文网

        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

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

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)

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

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

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

--ON CONFLICT 属性也能指定 WHERE

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

--参考

• INSERT COMMAND