阅读 395

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

引言

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

语法

INSERT INTO 表名 VALUES ('値1', '値2', ...)
ON CONFLICT ON CONSTRAINT 制約名
DO UPDATE SET 列1='値', 列2='値', ...;
复制代码

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;
复制代码