磐维数据库2.0系列：两种风格的upsert语法

磐维数据库，简称"PanWeiDB"。是中国移动信息技术中心首个基于中国本土开源数据库打造的面向ICT基础设施的自研数据库产品。其产品内核能力基于华为openGauss开源软件，并进一步提升了系统稳定性。

本文将介绍磐维数据库里的upsert语法及使用示例。

一、upsert概念

"upsert"是一个数据库功能，允许DML语句插入一行数据或者在现存行的基础上更新数据行，同时是保证并发安全性和原子性。

upsert相当于insert + update的组合，当数据行insert发生主键或唯一约束重复时，可触发update操作。

二、upsert语法

在PanWeiDB 1.0.0版本，upsert语法兼容Oracle语法风格，参考如下：

insert into ... ON DUPLICATE KEY  update columnN=excluded.columnN;

在PanWeiDB 2.0.0版本，upsert语法增加了兼容PG风格的形式：

insert into ... ON CONFLICT(XXX) do update set columnN=excluded.columnN;

三、PG风格的使用示例

兼容PG形式的upsert示例如下：

示例一：

create table test_upsert (id int primary key,code varchar unique,info varchar);

insert into test_upsert (id,code,info) 
    values (1,'code1','info');

上面插入一条主键id为1的数据后，再次插入id为1的数据时可使用upsert语句正常执行

insert into test_upsert (id,code,info) 
    values (1,'code1','info extra1') 
    on conflict(id) 
    do update set info=excluded.info,id=excluded.id;

insert into test_upsert (id,code,info) 
    values (1,'code1','info extra2') 
    on conflict(id) 
    do update set info=excluded.info,code=excluded.code;

upsert语句允许在update子句里更新主键id字段亦或唯一约束code字段。

示例二：

create table test_upsert2 (
    id int primary key,
    code1 varchar not null,
    code2 varchar not null,
    info varchar,
    unique(code1,code2)
);

insert into test_upsert2 (id,code1,code2,info) values (1,'MySQL','PanWeiDB','info');

test_upsert2表里使用了多字段唯一索引，下面upsert语句可以指定冲突的条件是unique(code1,code2)，而非主键id，同时在update子句也可对主键id或组合唯一索引的部分字段code1进行操作

insert into test_upsert2 (id,code1,code2,info) 
    values (1,'MySQL','PanWeiDB','db extra1') 
    on conflict(code1,code2) 
    do update set info=excluded.info, 
                  id=excluded.id,
                  code1=excluded.code1;

四、upsert的JDBC典型案例

案例：upsert与autoGeneratedKeys同时使用

connection.prepareStatement("insert into tab1(id,name) values(?,?) ON DUPLICATE KEY update name=excluded.name returning *", Statement.RETURN_GENERATED_KEYS);

上面的java代码里，使用insert on duplicate，同时打开了autoGeneratedKeys参数，则会出现如下错误：

ERROR: RETURNING clause is not yet supported within INSERT ON DUPLICATE KEY UPDATE statement.

经过测试oracle风格的upsert语句不能同时与autoGeneratedKeys参数使用。

在PanWeiDB 2.0.0版本里，PG兼容性模式下是可以正常使用。

首先在psql里进行测试，语法正常

panweidb=# insert into tab1(id,name) values(100,'panweidb') ON CONFLICT(id) do update set name=excluded.name returning *;
 id  |   name   
-----+----------
 100 | panweidb
(1 row)

INSERT 0 1

接着在java代码里进行如下测试，也能正常执行：

connection.prepareStatement("insert into tab1(id,name) values(?,?) ON CONFLICT(id) do update set name=excluded.name returning *", Statement.RETURN_GENERATED_KEYS);

注意需要使用磐维数据库2.0 JDBC驱动包，下载链接：

• panweidb-jdbc-2.0.0.jar

五、结论

通过上面upsert语法示例可以看出PanWeiDB 2.0.0版本里，upsert语法更加全面，同时支持openGauss风格和PG风格，通过典型案例也可以看到PG风格的upsert支持更加完善。