Try   HackMD

PostgreSQL update 案例

前言

在BackendTW社群中,有人問了這樣的問題:傳送門
簡言之就是兩筆record的一個欄位要交換.

SQL的作法

通常一聽到交換,就很容易聯想到使用變數來中繼或者叫過橋.
我們使用的是資料庫,與程序性語言的方式是不同的.
可以這樣做

target: 勇者(1), 某董(2), 小島南(3), 佐山愛(4)

source: 勇者(1), 某董(2), 小島南(3), 佐山愛(4)

然後 update target 

後面還有大大提出是不是要做log

先來直接看我寫的測試, 也有傳送門:按我前往

create table t240108a ( id int not null primary key , player text , cash bigint , Monologue text ); insert into t240108a values (1, '勇者', random() * 1e7, '喜歡邊喝咖啡,抽著煙,寫Prolog,Haskell,SQL.'), (2, '某董', random() * 1e13, '不要羨慕我,不要嫉妒我.'), (3, '小島南', null, '突然召喚我出來,你壞壞捏.等一下請吃鼎泰豐喔.'), (4, '佐山愛', null, '聽說有我的分享按讚數都不錯.等一下請吃鼎泰豐喔.'); create table t240108b ( id bigint generated always as identity primary key , card int , detail jsonb , ts timestamptz default clock_timestamp() ); --- create or replace procedure magicCard99(a int, b int) language sql as $code$ with t1 as ( update t240108a set cash = oldt.cash from t240108a as oldt where (t240108a.id, oldt.id) in ((a,b), (b,a)) returning t240108a.id, t240108a.cash, oldt.id as oldid, oldt.cash as oldcash ) insert into t240108b(card, detail) select 99 , jsonb_build_object('交換之前', json_agg(json_build_object(oldid, oldcash))) || jsonb_build_object('交換之後', json_agg(json_build_object(id, cash))) from t1; $code$; call magicCard99(1,2); select * from t240108a order by id; id | player | cash | monologue ----+--------+---------------+------------------------------------------------ 1 | 勇者 | 9540297216777 | 喜歡邊喝咖啡,抽著煙,寫Prolog,Haskell,SQL. 2 | 某董 | 5674863 | 不要羨慕我,不要嫉妒我. 3 | 小島南 | NULL | 突然召喚我出來,你壞壞捏.等一下請吃鼎泰豐喔. 4 | 佐山愛 | NULL | 聽說有我的分享按讚數都不錯.等一下請吃鼎泰豐喔. (4 rows) select * from t240108b; -[ RECORD 1 ]---------------------------------------------------------------------------------------------------- id | 1 card | 99 detail | {"交換之前": [{"2": 9540297216777}, {"1": 5674863}], "交換之後": [{"1": 9540297216777}, {"2": 5674863}]} ts | 2024-01-08 12:35:49.991599+08

update 之後再利用returning, 然後組合成json, insert into logtable.

結語

方法有很多,不一定哪種好.SQL 是集合的操作,與單筆操作不同.
還是要感謝一下小島南與佐山愛.