postgres 关于insert or update优化

执行批量的 insert

INSERT INTO users (id, level)
VALUES (1, 3), (2, 1)
ON CONFLICT (id) DO UPDATE
SET level = excluded.level;

我们看到已存在行(id=1) 的 level 更新为新的值 3, 并且插入新行。

这里注意到我们用到一个关键字 excluded 用于引用 INSERT 语句中 VALUES 提供的值，相应的表名 users 用于引用表中原有的值。

基于 unnest 的 upsert 操作

前面仍然是写作此文的意外收获，实际上想要总结的是 unnest 关键字在批量操作中的作用。下面来进行演示

insert into testunnest(id, col1, col2) 
 values (unnest(array[1,2]), unnest(array[30,40]), unnest(array['val1', '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 = excluded.col1, col2 = excluded.col2

unnest 与 JDBC 操作

insert into users values (?, ?) on conflict.... 的 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 = excluded.col1, col2 = excluded.col2"
);
 
pstmt.setArray(1, conn.createArrayOf("int", new Integer[]{2, 3}));
pstmt.setArray(2, conn.createArrayOf(JDBCType.INTEGER.getName(), new Integer[]{80, 90}));
pstmt.setArray(3, conn.createArrayOf("varchar", new String[]{"val1", "val2"}));
 
int update = pstmt.executeUpdate();
System.out.println(update); //影响的记录数是 2

点位符要用 unnest(?)，设置参数时要用 setArray(), 参数用 conn.createArrayOf(type, array) 来指定。需要指明数组中的元素类型，这么普通的 setInt(), setString() 是一个意思。

用不着转换为 PostgreSQL 特定的 PreparedStatement 来操作，用 JDBC 通用的 PreparedStatement 接口就能支持对数组类型的赋值