PostgreSQL实现批量插入、更新与合并操作的方法

前言

就在 2019 年 1 月份微软收购了 PostgreSQL 数据库的初创公司 CitusData, 在云数据库方面可以增强与 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有模有样的,然而试了一下根本就不支持 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 语法请参考官方文档 PostgreSQL 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
 (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 = 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 接口就能支持对数组类型的赋值,难道是其他类型的数据库也能支持类似的数组操作?

链接:

Upsert Records with PostgresSQL 9.5

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

(0)

相关推荐

  • Python随机生成数据后插入到PostgreSQL

    用Python随机生成学生姓名,三科成绩和班级数据,再插入到PostgreSQL中. 模块用psycopg2 random import random import psycopg2 fname=['金','赵','李','陈','许','龙','王','高','张','侯','艾','钱','孙','周','郑'] mname=['玉','明','玲','淑','偑','艳','大','小','风','雨','雪','天','水','奇','鲸','米','晓','泽','恩','葛','玄'

  • postgresql sql批量更新记录

    复制代码 代码如下: CREATE FUNCTION updateTchrNm() RETURNS void AS $body$ DECLARE rownum integer := 1; BEGIN while rownum <= 1000 LOOP     update t_tchr set tchr_nm = '田中愛子' || rownum, tchr_knm = 'タナカアイコ' || rownum, tchr_anm = 'tanaka' || rownum where tchr_cd

  • PostgreSQL实现批量插入、更新与合并操作的方法

    前言 就在 2019 年 1 月份微软收购了 PostgreSQL 数据库的初创公司 CitusData, 在云数据库方面可以增强与 AWS 的竟争.AWS 的 RDS 两大开源数据库就是 MySQL(Aurora 和 MariaDB 是它的变种) 和 PostgreSQL. 而 PostgreSQL 跳出了普通关系型数据库的类型约束,它灵活的支持 JSON, JSONB, XML, 数组等类型.比如说字段类型可以是各种形式的数组,一维或多维. create table t1( address

  • 实操MySQL+PostgreSQL批量插入更新insertOrUpdate

    目录 一.百度百科 1.MySQL 2.PostgreSQL 3.PostgreSQL相对于MySQL的优势 二.postgres中insertOrUpdate代码实例 1.创建user表 2.简单的方式实现 3.利用unnest函数实现 4.如果数据已存在,就就什么也不做 三.相关重点函数简介 1.unnest(anyarray) 四.userMapper.xml写法 五.MySQL中insertOrUpdate代码实例 1.建表语句 2.普通方式 3.ON DUPLICATE KEY UPD

  • 详解PostgreSQL提升批量数据导入性能的n种方法

    关键字:批量数据导入,数据加载,大量插入,加快,提升速度 多元化选择时代,人生里很多事物都是如此,凡事都没有一成不变的方式和方法.不管白猫黑猫,能抓老鼠的就是好猫,适合自己的就是最好的. 提升批量数据导入的方法亦是如此,没有何种方法是最优的,应用任何方法前根据自己的实际情况权衡利弊,做出选择. 批量导入数据之前,无论采取何种方式,务必做好相应的备份. 导入完成后亦需对相应对象进行ANALYZE操作,这样查询优化器才会按照最新的统计信息生成正确的执行计划. 下面正式介绍提升批量数据导入性能的n种方

  • js中数组插入、删除元素操作的方法

    实例如下: /* * 删除数组元素:Array.removeArr(index) */ Array.prototype.removeArr = function (index) { if (isNaN(index) || index>= this.length) { return false; } this.splice(index, 1); } /* * 插入数组元素:Array.insertArr(dx) */ Array.prototype.insertArr = function (in

  • PostgreSQL upsert(插入更新)数据的操作详解

    本文介绍如何使用PostgreSQL upsert特性插入或当被插入数据已存在则更新数据. 1. 介绍PostgreSQL upsert 在关系型数据库中,upsert是一个组合词,即当往表中插入记录,如果该记录已存在则更新,否则插入新记录.为了使用该特性需要使用INSERT ON CONFLICT语句: INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT target action; 该语法中target可以是下

  • Mybatis批量插入更新xml方式和注解方式的方法实例

    前言 最近工作上遇到很多批量插入的场景,但是百度很难得到我想要的结果,而且查出来的效果不是很好- 所以就自己来写一份给大家参考,希望对大家有用 Mybatis 批量插入注解形式 @Insert("<script> INSERT INTO t_device_policy " + "(id,device_id,type,policy,create_time,update_time) " + "VALUES " + "<fo

  • MySQL批量插入遇上唯一索引避免方法

    一.背景 以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题:Step8:SQL Server 当表分区遇上唯一约束,没想到在MySQL的分区中一样会遇到这样的问题:MySQL表分区实战. 今天我们来了解MySQL唯一索引的一些知识:包括如何创建,如何批量插入,还有一些技巧上SQL: 这些问题的根源在什么地方?有什么共同点?MySQL中也有分区对齐的概念?唯一索引是在很多系统中都会出现的要求,有什么办法可以避免?它对性能的影响有多大? 二.过程 (一) 导入差异数据,忽略重

  • 通过反射注解批量插入数据到DB的实现方法

    批量导入思路 最近遇到一个需要批量导入数据问题.后来考虑运用反射做成一个工具类,思路是首先定义注解接口,在bean类上加注解,运行时通过反射获取传入Bean的注解,自动生成需要插入DB的SQL,根据设置的参数值批量提交.不需要写具体的SQL,也没有DAO的实现,这样一来批量导入的实现就和具体的数据库表彻底解耦.实际批量执行的SQL如下: insert into company_candidate(company_id,user_id,card_id,facebook_id,type,create

  • Android批量插入数据到SQLite数据库的方法

    Android中在sqlite插入数据的时候默认一条语句就是一个事务,因此如果存在上万条数据插入的话,那就需要执行上万次插入操作,操作速度可想而知.因此在Android中插入数据时,使用批量插入的方式可以大大提高插入速度. 有时需要把一些数据内置到应用中,常用的有以下几种方式: 1.使用db.execSQL(sql) 这里是把要插入的数据拼接成可执行的sql语句,然后调用db.execSQL(sql)方法执行插入. public void inertOrUpdateDateBatch(List<

  • python文件读写并使用mysql批量插入示例分享(python操作mysql)

    复制代码 代码如下: # -*- coding: utf-8 -*-'''Created on 2013年12月9日 @author: hhdys''' import osimport mysql.connector config = {  'user': 'root',  'password': '******',  'host': '127.0.0.1',  'database': 'test',  'raise_on_warnings': True,}cnx = mysql.connect

随机推荐