以Postgresql为主,再聊聊资料库 PostgreSQL 多笔 update 方式探讨

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 传入.