【GaussDB每日一贴】GaussDB/postgresql 中on duplicat key语句的用法

建一个有主键的表

CREATE TABLE teacher(id int,col1 text,col2 text,primary key(id));
insert into teacher values(1,'tom','12312');
insert into teacher values(2,'jerry','54335');
insert into teacher values(3,'jack','88888');

gaussdb=> select * from teacher;
 id | col1  | col2
----+-------+-------
  1 | tom   | 12312
  2 | jerry | 54335
  3 | jack  | 88888

当出现主键冲突时，执行插入语句是会报错的

gaussdb=> insert into teacher values(2,'tom','jerry');
ERROR:  duplicate key value violates unique constraint "teacher_pkey"
DETAIL:  Key (id)=(2) already exists.

可以加上on duplicate key，其作用是如果有主键冲突的时候，对有冲突的原有的元组进行update操作

gaussdb=> insert into teacher values(2,'tom','jerry') on duplicate key update col1='AAAAAAAAAAA';
INSERT 0 1
gaussdb=> select * from teacher;
 id |    col1     | col2
----+-------------+-------
  1 | tom         | 12312
  2 | AAAAAAAAAAA | 54335
  3 | jack        | 88888