PostgreSQL　使用UPSERT 数据不存在则插入，存在则更新

引言

该功能由PostgreSQL9.5 开始支持。在此之前如果想插入一条数据，该数据的主键已经存在，则无法插入。使用upset句，可以无则插入，有则更新。

语法

INSERT INTO 表名 VALUES ('値１', '値２', ...）
ON CONFLICT ON CONSTRAINT 制約名
DO UPDATE SET 列１='値', 列２='値', ...;

1. 尝试Upset

首先，创建一个表，并插入一条数据。

create table m_user (
  username character varying(8) not null
  , password character varying(16)
  , auth character varying(16)
  , primary key (username)
);

insert into m_user values('0001','0001','admin')；

然后调查该表的制约名。

select table_name, constraint_name, constraint_type
from   information_schema.table_constraints
where  table_name='m_user';

可以发现该制约名为m_user_pkey。

当然，也可以在创建表时，就自定义制约名。

create table m_user (
  username character varying(8) not null
  , password character varying(16)
  , auth character varying(16)
  , constraint m_user_pkey primary key (username)
);

尝试执行以下语句，制约名指定m_user_pkey。会发现即使主键0001的username已经存在，则把他的password改为了0002。

执行语句

insert into m_user values('0001','0002','admin')
on conflict on constraint m_user_pkey
do update set password='0002';

执行结果

 username | password | auth
----------+----------+-------
 0001     | 0002     | admin
(1 行)

2. 尝试使用 DO NOTHING

继续对刚才的表进行操作，会发现执行结果没有更新任何数据，但是避免了主键重复错误。如果在开发中有这种场景又不想出错的话可以使用do nothing。

执行语句

insert into m_user values('0001','0002','admin')
on conflict on constraint m_user_pkey
do nothing;