PostgreSQL实现批量插⼊、更新与合并操作的⽅法
前⾔
就在 2019 年 1 ⽉份微软收购了 PostgreSQL 数据库的初创公司 , 在云数据库⽅⾯可以增强与 AWS 的竟争。AWS 的 RDS 两⼤开源数据库就是 MySQL(Aurora 和 MariaDB 是它的变种) 和 PostgreSQL。
⽽ PostgreSQL 跳出了普通关系型数据库的类型约束，它灵活的⽀持 JSON, JSONB, XML, 数组等类型。⽐如说字段类型可以是各种形式的数组，⼀维或多维。
create table t1(
address varchar(5)[3],
counter integer[3][3],
schedule text[][]
)
上⾯只是认识了⼀下 PostgreSQL 这⼀亮眼的特性，本篇重点不在如何定义操作数组类型的字段，⽽是
对于普通的⾮数组字段类型如何⽤与数组相关的 unnest 关键字进⾏记录的批量插⼊，更新以及合并操作。
在正式使⽤介⼊ unnest 之前先熟悉⼀下 PostgreSQL 的 upsert(update insert) 操作。受其他数据库的影响，总以后PostgreSQL 也应该⽀持 merge into 语句，⽽且竟然 PostgreSQL 官⽅也有⽂档介绍有模有样的，然⽽试了⼀下根本就不⽀持 merge into 操作。
普通的 insert upsert 操作
不过 PostgreSQL 有另⼀种 upsert 的语法，或称之为 insert on conflict，也就是 PostgreSQL 加强版的 insert 语句。如下
INSERT INTO users (id, level)
VALUES (1, 0)
ON CONFLICT (id) DO UPDATE
SET level = users.level + 1;
即插⼊记录时存在相同的主键可对原记录的某些列进⾏更新，否则插⼊该记录。更详细的 insert 语法请参考官⽅⽂档。
注意到上⾯的 upsert 只是应⽤于处理单条记录，⽽本⽂将要讨论到的是批量的处理。我们知道 insert 时可以同时插⼊多⾏记录，那么这个  upsert 语法是否也能同时操作多⾏记录呢？我们⼤胆假设，⼩⼼求证，那就动⼿⼀试吧
create table users(
id int primary key,
level int
)
创建⼀个表 users, 然后插⼊⼀条记录
INSERT INTO users (id, level) VALUES (1, 0)
查询select * from users 表的内容如下
执⾏批量的 insert
INSERT INTO users (id, level)
VALUES (1, 3), (2, 1)
ON CONFLICT (id) DO UPDATE
SET level = excluded.level;
理看看 users 表中的内容
我们看到已存在⾏(id=1) 的 level 更新为新的值 3, 并且插⼊新⾏。
这⾥注意到我们⽤到⼀个关键字 excluded ⽤于引⽤ INSERT 语句中 VALUES 提供的值，相应的表名 users ⽤于引⽤表中原有的值。
基于 unnest 的 upsert 操作
前⾯仍然是写作此⽂的意外收获，实际上想要总结的是 unnest 关键字在批量操作中的作⽤。下⾯来进⾏演⽰
create table testunnest(
id int primary key,
col1 int,
col2 varchar(50)
)
创建⼀个表并⽤ unnest 提供并⾏数组来进⾏批量插⼊
insert into testunnest(id, col1, col2)
values (unnest(array[1,2]), unnest(array[30,40]), unnest(array['val1', 'val2']));
这时候表中的内容为
从执⾏效果来看，它与下⾯的 insert 语句是等效的
insert into testunnest(id, col1, col2) values批量更新sql语句
(1, 30, 'val1')
(2, 40, 'val2')
换成成 unnest(array[..]) 的形式有⼀种⾏转列的⾏为。
⽤ unnest 加上 unsert 再执⾏⼀次插⼊
insert into testunnest(id, col1, col2)
values (unnest(array[2,3]), unnest(array[80,90]), unnest(array['valupdated', 'val3']))
on conflict (id) do update
set col1 = l1, col2 = l2
再查看表的内容如下
unnest 与 JDBC 操作
insert into users values (?, ?) 的 SQL 语句的单条记录或批量操作(addBatch(), executeBatch()) 就不多说了，主要看下⽤ JDBC 怎么对 unnest 进⾏赋值操作。
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO testunnest(id, col1, col2) " +
" VALUES (unnest(?), unnest(?), unnest(?))" +
" ON CONFLICT (id) DO UPDATE" +
" SET col1 = l1, col2 = l2"
);
pstmt.setArray(1, ateArrayOf("int", new Integer[]{2, 3}));
pstmt.setArray(2, ateArrayOf(Name(), new Integer[]{80, 90}));
pstmt.setArray(3, ateArrayOf("varchar", new String[]{"val1", "val2"}));
int update = uteUpdate();
System.out.println(update); //影响的记录数是 2
点位符要⽤ unnest(?)，设置参数时要⽤ setArray(), 参数⽤ ateArrayOf(type, array) 来指定。需要指明数组中的元素类型，这么普通的 setInt(), setString() 是⼀个意思。
⽤不着转换为 PostgreSQL 特定的 PreparedStatement 来操作，⽤ JDBC 通⽤的 PreparedStatement 接⼝就能⽀持对数组类型的赋值，难道是其他类型的数据库也能⽀持类似的数组操作？
链接：
总结
以上就是这篇⽂章的全部内容了，希望本⽂的内容对⼤家的学习或者⼯作具有⼀定的参考学习价值，如果有疑问⼤家可以留⾔交流，谢谢⼤家对的⽀持。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。