前言
就在 2019 年 1 月份微軟收購了 PostgreSQL 數據庫的初創公司 CitusData, 在云數據庫方面可以增強與 AWS 的竟爭。AWS 的 RDS 兩大開源數據庫就是 MySQL(Aurora 和 MariaDB 是它的變種) 和 PostgreSQL。
而 PostgreSQL 跳出了普通關系型數據庫的類型約束，它靈活的支持 JSON, JSONB, XML, 數組等類型。比如說字段類型可以是各種形式的數組，一維或多維。
|
1
2
3
4
5
|
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 語句。如下
|
1
2
3
4
|
INSERT INTO users (id, level)VALUES (1, 0)ON CONFLICT (id) DO UPDATESET level = users.level + 1; |
即插入記錄時存在相同的主鍵可對原記錄的某些列進行更新，否則插入該記錄。更詳細的 insert 語法請參考官方文檔 PostgreSQL INSERT。
注意到上面的 upsert 只是應用于處理單條記錄，而本文將要討論到的是批量的處理。我們知道 insert 時可以同時插入多行記錄，那么這個 upsert 語法是否也能同時操作多行記錄呢？我們大膽假設，小心求證，那就動手一試吧
|
1
2
3
4
|
create table users( id int primary key, level int) |
創建一個表 users, 然后插入一條記錄
|
1
|
INSERT INTO users (id, level) VALUES (1, 0) |
查詢 select * from users 表的內容如下
執行批量的 insert
|
1
2
3
4
|
INSERT INTO users (id, level)VALUES (1, 3), (2, 1)ON CONFLICT (id) DO UPDATESET level = excluded.level; |
理看看 users 表中的內容
我們看到已存在行(id=1) 的 level 更新為新的值 3, 并且插入新行。
這里注意到我們用到一個關鍵字 excluded 用于引用 INSERT 語句中 VALUES 提供的值，相應的表名 users 用于引用表中原有的值。
基于 unnest 的 upsert 操作
前面仍然是寫作此文的意外收獲，實際上想要總結的是 unnest 關鍵字在批量操作中的作用。下面來進行演示
|
1
2
3
4
5
|
create table testunnest( id int primary key, col1 int, col2 varchar(50)) |
創建一個表并用 unnest 提供并行數組來進行批量插入
|
1
2
|
insert into testunnest(id, col1, col2) values (unnest(array[1,2]), unnest(array[30,40]), unnest(array['val1', 'val2'])); |
這時候表中的內容為
從執行效果來看，它與下面的 insert 語句是等效的
|
1
2
3
|
insert into testunnest(id, col1, col2) values (1, 30, 'val1') (2, 40, 'val2') |
換成成 unnest(array[..]) 的形式有一種行轉列的行為。
用 unnest 加上 unsert 再執行一次插入
|
1
2
3
4
|
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 進行賦值操作。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對服務器之家的支持。
原文鏈接：https://yanbin.blog/postgresql-unnest-batch-crud-merge/
