insert、update、delete 三個指令有兩個共通點：

  • 都支援 returning 子句——讓 DML 指令像 select 一樣回傳結果集（兩者都是投影）。這是 PostgreSQL 對 SQL 標準的擴充，語意乾淨通用，還能省掉一趟網路往返：應用程式不必再查一次就知道資料庫選了什麼預設值。
  • 都有自己的方式可以做 join——只是三個語句各自的寫法不同，且同樣屬於 SQL 標準。

Insert Into#

以推文模型來說，首先需要使用者。values 子句是 SQL 標準的一部分，凡是能放 select 的地方都能用，而且一次可以接受多列：

insert into tweet.users (userid, uname, nickname, bio)
     values (default, 'Theseus', 'Duke Theseus', 'Duke of Athens.');

接著用單一 insert … values 一次插入其餘角色（Egeus、Lysander、Demetrius、Hippolyta、Hermia、Helena、Oberon、Titania、Puck、眾精靈等 28 位）——名字與簡介取自莎士比亞《仲夏夜之夢》的角色表：

insert into tweet.users (uname, bio)
     values ('Egeus', 'father to #Hermia.'),
            ('Lysander', 'in love with #Hermia.'),
            ('Demetrius', 'in love with #Hermia.'),
            -- ...共 28 列
            ('Moonshine', 'a play within a play');

要插入大量資料列時，優先考慮 copy 指令而非一連串 insert；若因故不能用 copy，基於效能考量，請在單一交易內執行多個 insert，且每個 insert 帶多列 values。

Insert Into … Select#

insert 也能以查詢作為資料來源——這正是 insert 實作 join 的方式。例如從 bio 欄位找出「誰愛著誰」來填 tweet.follower 表。先寫出資料來源查詢：

select users.userid as follower,
       users.uname,
       f.userid as following,
       f.uname
  from      tweet.users
       join tweet.users f
         on f.uname = substring(users.bio from 'in love with #?(.*).')
 where users.bio ~ 'in love with';

substring 表達式只回傳正規表達式的匹配群組，也就是使用者所愛之人的名字。確認結果正確後，直接把 select 餵給 insert：

insert into tweet.follower
     select users.userid as follower,
            f.userid as following
       from      tweet.users
            join tweet.users f
              on f.uname = substring(users.bio from 'in love with #?(.*).')
      where users.bio ~ 'in love with';

再讓精靈們追蹤他們的國王與女王，這次還用上 cross join 產生「每個精靈 × 每位王族」的所有組合：

with fairies as
 (
   select userid
     from tweet.users
    where bio ~ '#Fairies'
 )
 insert into tweet.follower(follower, following)
      select fairies.userid as follower,
             users.userid as following
        from fairies cross join tweet.users
       where users.bio ~ 'of the fairies';
查看目前的追蹤關係
select follower.uname as follower,
       follower.bio as "follower's bio",
       following.uname as following
  from tweet.follower as follows
       join tweet.users as follower
         on follows.follower = follower.userid
       join tweet.users as following
         on follows.following = following.userid;
   follower   │               follower's bio              │ following
══════════════╪═══════════════════════════════════════════╪═══════════
 Hermia       │ daughter to Egeus, in love with Lysander. │ Lysander
 Helena       │ in love with Demetrius.                   │ Demetrius
 Demetrius    │ in love with #Hermia.                     │ Hermia
 Lysander     │ in love with #Hermia.                     │ Hermia
 Peaseblossom │ Team #Fairies                             │ Oberon
 ...
(12 rows)

Update#

update 用來替換資料庫中的既有值，其最重要的面向在於並行行為：它允許在其他使用者同時操作資料庫的情況下替換資料。

  • PostgreSQL 的並行功能全部建立在 MVCC（多版本並行控制）之上：update 在內部其實是插入新資料＋刪除舊資料。系統欄位 xmin、xmax 追蹤資料列的可見性，讓並行語句隨時擁有一致的資料快照。
  • PostgreSQL 的資料列鎖定是 per-tuple：一個 update 只會阻擋鎖定同一批資料列的其他 update、delete 或 select for update。

單筆更新的典型寫法——以主鍵查找，並用 returning 確認結果：

begin;

   update tweet.users
      set nickname = 'Robin Goodfellow'
    where userid = 17 and uname = 'Puck'
returning users.*;

commit;
 userid │ uname │     nickname     │         bio          │ picture
════════╪═══════╪══════════════════╪══════════════════════╪═════════
     17 │ Puck  │ Robin Goodfellow │ or Robin Goodfellow. │ ¤
(1 row)

注意 where 條件不只用了主鍵，還加上了真正關心的值 uname = 'Puck'——因為主鍵是合成鍵（synthetic key）。這個雙重檢查有兩個作用：

  • 若 id 貼錯了，update 找不到符合列，影響零筆。
  • 並行防護：若有人在我們執行期間改掉了 Puck 的 uname，那麼只有兩種可能——對方先到，名字已不是 Puck，我們更新了零筆；或對方後到，我們確實更新了「userid 17 且名為 Puck」的那一列。

在應用程式碼裡處理並行時記得這個雙重檢查技巧；在主控台手動修資料做一次性修復時更要用——永遠包在明確的交易區塊裡，確認結果不對就 rollback;。

update 也能一次更新多列，且可以在更新中使用既有資料。例如為所有還沒有暱稱的角色計算一個預設暱稱：

   update tweet.users
      set nickname = case when uname ~ ' '
                           then substring(uname from '[^ ]* (.*)')
                           else uname
                      end
    where nickname is null
returning users.*;
更新後的使用者一覽（節錄）
  select uname, nickname, bio
    from tweet.users
order by userid;
       uname      │     nickname     │                     bio
══════════════════╪══════════════════╪═════════════════════════════════════════════
 Theseus          │ Duke Theseus     │ Duke of Athens.
 Peter Quince     │ Quince           │ a carpenter.
 Nick Bottom      │ Bottom           │ a weaver.
 Puck             │ Robin Goodfellow │ or Robin Goodfellow.
 ...
(29 rows)

名字與簡介取自莎士比亞《仲夏夜之夢》，Jon Bosak 的 Shakespeare 2.00 提供了完整的 XML 文本。

插入一些推文#

《仲夏夜之夢》的 XML 文本不只有角色表，還有完整台詞——每個角色都是 speaker、都有台詞，正好當推文內容。文本格式如下（節錄）：

<SPEECH>
<SPEAKER>THESEUS</SPEAKER>
<LINE>Now, fair Hippolyta, our nuptial hour</LINE>
<LINE>Draws on apace; four happy days bring in</LINE>
</SPEECH>

寫一個簡單的 XML 解析器搭配這條 insert 查詢載入。因為劇本用 QUINCE 這類簡稱而資料庫存的是全名，所以同時比對 uname 與 nickname：

insert into tweet.message(userid, message)
     select userid, $2
       from tweet.users
      where users.uname = $1 or users.nickname = $1

載入後就能用 SQL 看到莎士比亞角色們開始「發推」（select … from tweet.message left join tweet.users using(userid) order by messageid limit 4; 會列出 Theseus 與 Hippolyta 的開場對白）。

Delete#

delete 語句是把 tuple 標記為待移除。基於 PostgreSQL 的 MVCC 實作，在 delete 當下就從磁碟移除資料並不明智：

  • 交易可能會 rollback，此刻還不知道；
  • 其他並行交易要等 commit 之後才能看到刪除，而不是語句一執行完就看到。

與 update 一樣，delete 最重要的部分也是並行——我們用 RDBMS 的主要理由，就是不必在應用程式碼裡自己解決並行問題。磁碟上 tuple 的實際移除由 vacuum 完成，系統的 autovacuum 背景程序會自動處理；當某個 tuple 對所有交易都不可見後，PostgreSQL 也可能直接把該磁碟空間重用給新的 insert。

假設我們誤加了《哈姆雷特》的角色（CLAUDIUS、HAMLET、POLONIUS、HORATIO、LAERTES、LUCIANUS），單筆刪除的寫法同樣建議用雙重檢查＋returning：

begin;

   delete
      from tweet.users
    where userid = 22 and uname = 'CLAUDIUS'
 returning *;

commit;

一次刪除多列則可以用 anti-join：這些誤插的角色在劇中沒有台詞，據此刪除，並用 CTE 包起來輸出摘要：

begin;

with deleted_rows as
 (
     delete
       from tweet.users
      where not exists
            (
              select 1
                from tweet.message
               where userid = users.userid
            )
  returning *
 )
select min(userid), max(userid),
       count(*),
       array_agg(uname)
  from deleted_rows;

commit;
 min │ max │ count │                 array_agg
═════╪═════╪═══════╪════════════════════════════════════════════
  41 │  45 │     5 │ {HAMLET,POLONIUS,HORATIO,LAERTES,LUCIANUS}
(1 row)

這種「CTE 包 delete + returning + 摘要輸出」的寫法，應該成為你在任何資料庫互動式執行 delete 的預設語法。

delete 也支援 join 條件，寫法是 using，詳見 PostgreSQL 的 delete 文件。

Tuple 與 Row#

本章交替提到 tuple 與 row，兩者有別：同一個 row 可能同時以多個 tuple 的形式存在磁碟上，而任一交易只看得到其中一個。

  • 執行 update 的交易看到的是新版本的 row（剛插入磁碟的新 tuple）；
  • 只要該交易尚未 commit，世界上其他人看到的仍是舊版本的 row（磁碟上的另一個 tuple）。

某些情境下兩詞可互換，但在討論 DML 時必須放對語境。

刪除全部資料列：Truncate#

PostgreSQL 額外提供 truncate 指令（內部歸類為 DDL 而非 DML）。它不走 per-tuple 的 MVCC 流程、直接移除磁碟上的資料檔，因此是一口氣清空整張表最有效率的方式。

select count(*) from foo;

begin;
truncate foo;
rollback;

select count(*) from foo;

在沒有並行活動的前提下，前後兩次計數會相同。

刪除但保留少數資料列#

清理資料時常會遇到要移除表中大部分內容的情況（過期的 log 表、audit trail 等）。既然 delete 只是標記不可見、重活留給 vacuum，更有效率的做法是：建一張只含要保留資料的新表，然後跟舊表交換。

begin;

create table new_name (like name including all);

insert into new_name
      select <column list>
        from name
       where <restrictions>;

drop table name;
alter table new_name rename to name;

commit;

一般而言，只要是移除表中大多數資料，這個方法都更有效率。

這招的代價是鎖定等級：drop table 與 alter table 需要 access exclusive lock，執行期間會阻擋兩張表的所有讀寫流量。如果你的系統沒有離峰或停機時段，這個技巧可能不可行；而 delete + vacuum 的好處正是幾乎可以在任何並行流量下照常執行。

Backward 另一個小型應用 隔離與鎖定 Forward