PostgreSQL 多筆 update 方式探討

前言

看到FB上 Backend 台灣 (Backend Tw) 中 Sam Wong大大發表這篇
https://www.facebook.com/groups/616369245163622/posts/2540355279431666/

裡面有提到使用 upsert 的方式.
來做些測試與探討.

傳統方式

-- 建立測試table
create table it1208a (
  id int generated always as identity primary key
, k text 
, v int
);

-- 建立臨時表或是使用 select union all 或是 用 values 直接輸入
-- 再搭配 update from where

with t1(key,val) as (
select 'a', 3
union all
select 'b', 5
union all
select 'c', 7
)
update it1208a
   set v = v + val
  from t1
 where k = key;

select *
  from it1208a;

 id | k | v  
----+---+----
  1 | a | 13
  2 | b | 25
  3 | c | 37
(3 rows)

-- 使用 values 方式的部分
with t1 (key, val) as (
values
('a',3),('b',5),('c',7)
)
select *
  from t1;

  from t1;
 key | val 
-----+-----
 a   |   3
 b   |   5
 c   |   7
(3 rows)

傳統方式較為繁瑣,臨時表的方式,就是使用 create temp table, 再insert資料.為節省篇幅,在此省略.

upsert 方式

在PostgreSQL 發展過程中,在9.5版開始支援此方式.
語法是 INSERT, ON CONFLICT UPDATE 這樣的方式.
但是有一個前提,是必須有 unique.

create table it1208 (
  id int generated always as identity primary key
, k text not null unique
, v int
);

insert into it1208(k,v) values
('a', 10), ('b', 20), ('c', 30);

insert into it1208(k,v) values
('a', 3), ('b', 5), ('c', 7)
on conflict (k) 
do update
set v = it1208.v + excluded.v;

select *
  from it1208;
 id | k | v  
----+---+----
  1 | a | 13
  2 | b | 25
  3 | c | 37
(3 rows)

此方式的優點是在輸入時,可以一直使用此方式,當沒有 k 時就insert,當有 k 時改做update. 注意到有宣告 k 的 unique.

是否有另外的方式?

當不同的情境,例如 k 不是 unique 時,像是我們要對某些類別都增加額度時,或是想建立 function 做這類大量 update 操作.
雖然 upsert 可以很方便的使用 values , 但畢竟是語法的一部分,
需要組 Dynamic SQL.

PostgreSQL 的黑科技

PostgreSQL 與其他資料庫相比,有特殊的如 array 的資料型態.
而搭配array 有set returning functions , 如 unnest().
在 9.4 版開始在select 中支援 rows from

ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

上面提到的array 是存放單一型態,若要多種型態,可以使用json, jsonb.
看來我們可以使用這些黑科技,搭配組合.直接來看例子.

create table it1208b (
  id int generated always as identity primary key
, k text 
, v int
);

insert into it1208b(k,v) values
('a', 10), ('b', 20), ('c', 30);

select * from it1208b;

 id | k | v  
----+---+----
  1 | a | 10
  2 | b | 20
  3 | c | 30
(3 rows)


update it1208b
   set v = v + y.val
  from (select key, value::int as val
          from rows from 
          (jsonb_each('{"a": 3, "b":5, "c": 7}'::jsonb)
          ) x (key,value) ) y
 where k = y.key;

select * from it1208b;

 id | k | v  
----+---+----
  1 | a | 13
  2 | b | 25
  3 | c | 37
(3 rows)

這樣不需要 unique 也能多筆資料 update.
但是注意到此方式就是 update 而已.情境並不完全相同.

建立 function 的例子

create or replace function f_update_it1208b(injs jsonb)
returns void
language sql as
$code$
  update it1208b
   set v = v + y.val
  from (select key, value::int as val
          from rows from (jsonb_each(injs)
          ) x (key,value) ) y
 where k = y.key;
$code$;

-- 可以傳入 jsonb 
select f_update_it1208b(j)
  from (select '{"a": 1, "c": 3}'::jsonb
        union all
        select '{"b": 2}'::jsonb) x(j);

select * from it1208b;

 id | k | v  
----+---+----
  1 | a | 14
  3 | c | 40
  2 | b | 27
(3 rows)

此函數使用的language 是 sql, 不是 pl/pgsql.
不需要做語法組合,只需要外部組好 jsonb 傳入.

結語

資料庫的應用方式很靈活,效能與便捷性的提升是透過點點滴滴的研究探討,
在此例子中,可以看到PostgreSQL的版本演進逐漸增加功能,帶給我們廣大使用者許多的強大功能.