在 PostgreSQL 15 或更新版本中，使用宣告為 DEFERRABLE 的 UNIQUE NULLS NOT DISTINCT 約束，然後在重新排序交易中延後檢查。這樣文章資料列就能交換位置，同時確保最終順序唯一，包括父項為 NULL 的資料列。PostgreSQL 約束語法

本教學適用於在 Mac Terminal 工作、需要重新排列單一父項群組內合成文章資料列的開發者。內容包含 SQL 示範、可驗證要求順序的完整 node-postgres 程式，以及 PostgreSQL 14 的替代方案。本範例不涵蓋變更文章的父項。

為什麼可為 NULL 的父項會讓位置唯一性變複雜？

假設每篇文章都有 id、可為 NULL 的 parent_id，以及正整數 position。父項相同的文章會形成一個有序的同層群組。在此模型中，所有父項為 null 的文章都屬於同一個根群組。

單純的 UNIQUE (parent_id, position) 無法強制遵守根群組規則。PostgreSQL 通常在唯一性檢查中將 null 視為彼此不同，因此兩篇根文章可以同時占用位置 1。加入 NULLS NOT DISTINCT 後，檢查複合鍵時會將這些 null 父項值視為相同。不同的非 null 父項仍可各自擁有位置 1。PostgreSQL：唯一約束

重新排序還會帶來另一個時序問題。如果文章 1 位於位置 1，而文章 2 位於位置 2，將文章 2 移至位置 1 時，會暫時與文章 1 的位置重複。非延後唯一約束會立即檢查每筆新增或修改的資料列。延後約束則可等到陳述式結束時檢查，或在明確延後時等到交易結束才檢查。PostgreSQL：CREATE TABLE

若重新排序是透過多個更新完成，這兩項特性都很重要：識別 null 的唯一性規則定義正確的群組，而延後檢查則允許多個更新之間暫時發生位置衝突。

依據 PostgreSQL 版本，應使用哪種定義？

PostgreSQL 15 為唯一約束和索引引入了 NULLS NOT DISTINCT。以下主要範例要求伺服器版本為 15 或更新版本；PostgreSQL 14 則需使用後文所示的替代方案。請檢查目前連線的伺服器，而不要從已安裝的命令列用戶端推測伺服器版本。PostgreSQL 15 版本資訊

情況 支援的做法
PostgreSQL 15 或更新版本，新資料表 使用命名的 UNIQUE NULLS NOT DISTINCT 約束，並設為 DEFERRABLE INITIALLY IMMEDIATE
現有的一般唯一約束 替換其定義；ALTER CONSTRAINT 無法變更唯一約束的延後屬性
現有的運算式或部分唯一索引 替換其強制執行方式；此類索引無法附加為一般唯一約束
PostgreSQL 14 使用產生的分組欄位搭配可延後唯一約束

ALTER TABLE 文件說明了現有物件的限制：以此方式變更約束屬性僅適用於外鍵，而附加為唯一約束的索引不能包含運算式，也不能是部分索引。獨立的 CREATE UNIQUE INDEX 也沒有 DEFERRABLE 子句。PostgreSQL：ALTER TABLE、PostgreSQL：CREATE INDEX

如何在 SQL 中交換兩篇根文章的位置？

先決條件是已有 PostgreSQL 15 或更新版本的本機資料庫，且 psql 連線可正常運作。開啟 Mac Terminal，使用已設定的連線設定啟動用戶端。以下命令會略過啟動自訂設定，並在發生錯誤時停止執行指令碼。PostgreSQL：psql

psql -X -v ON_ERROR_STOP=1

將下方完整的 SQL 示範貼到該工作階段中。它會建立一個只包含合成文章的暫存資料表；工作階段結束時，暫存資料表就會消失。該工作階段中不可已有同名資料表。PostgreSQL：暫存資料表

SHOW server_version;

CREATE TEMP TABLE article_order_demo (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    CONSTRAINT article_order_demo_parent_position_key
        UNIQUE NULLS NOT DISTINCT (parent_id, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo (id, parent_id, title, position)
VALUES
    (1, NULL, 'Article A', 1),
    (2, NULL, 'Article B', 2),
    (3, NULL, 'Article C', 3),
    (4, 1, 'Article A: Overview', 1),
    (5, 1, 'Article A: Examples', 2);

BEGIN;
SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED;

UPDATE article_order_demo
SET position = 1
WHERE id = 2 AND parent_id IS NULL;

UPDATE article_order_demo
SET position = 2
WHERE id = 1 AND parent_id IS NULL;

SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo
WHERE parent_id IS NULL
ORDER BY position;

第一次更新後，交易中會暫時有兩篇根文章位於位置 1。第二次更新後，它們的位置會再次恢復唯一。將約束切回 IMMEDIATE 時，會在該時點檢查尚未驗證的變更。若仍有重複，該命令就會失敗；否則交易即可提交。延後檢查會改變驗證發生的時機。PostgreSQL：SET CONSTRAINTS

預期的根文章最終順序如下：

位置 文章 ID 標題
1 2 文章 B
2 1 文章 A
3 3 文章 C

子文章會保留原有位置，因為兩次更新都未選取它們。如果互動式交易遇到錯誤，請先輸入 ROLLBACK; 再重試。交易會將相關陳述式一併納入，直到 COMMIT 或 ROLLBACK 為止。PostgreSQL：BEGIN

如何使用 node-postgres 重新排序完整的同層群組？

整個交易都要使用同一個已取出的用戶端。node-postgres 明確要求如此：不同的 pool.query() 呼叫可能使用不同連線，因此不適合用於交易。node-postgres：交易

下方程式接受完整且有序的同層 ID 清單。它採用的額外規則很簡單：提供的清單必須是目前同層集合的排列。拒絕重複項目、遺漏項目以及來自其他群組的 ID，可避免看似成功但實際只完成部分的重新排序。

在本機 Node.js 專案中，安裝 pg，將下方完整程式存為 reorder.cjs，然後執行。程式假設現有的本機連線設定可透過 PostgreSQL 環境變數取得。node-postgres 支援與 psql 相同的連線環境變數。node-postgres：連線

npm install pg
node reorder.cjs

const { Pool } = require('pg');

function validateOrder(ids, rows) {
  if (!Array.isArray(ids) || !ids.every(Number.isSafeInteger) ||
      new Set(ids).size !== ids.length) {
    throw new Error('Order must contain distinct integer IDs');
  }
  const existing = new Set(rows.map(row => row.id));
  if (rows.length !== ids.length || ids.some(id => !existing.has(id))) {
    throw new Error('Order must include every sibling exactly once');
  }
}

async function main() {
  const pool = new Pool();
  let client;
  try {
    client = await pool.connect();
    await client.query(`
      CREATE TEMP TABLE article_order_demo (
        id integer PRIMARY KEY,
        parent_id integer REFERENCES article_order_demo(id),
        title text NOT NULL,
        position integer NOT NULL CHECK (position > 0),
        CONSTRAINT article_order_demo_parent_position_key
          UNIQUE NULLS NOT DISTINCT (parent_id, position)
          DEFERRABLE INITIALLY IMMEDIATE
      )
    `);
    await client.query(`
      INSERT INTO article_order_demo (id, parent_id, title, position)
      VALUES (1, NULL, 'Article A', 1),
             (2, NULL, 'Article B', 2),
             (3, NULL, 'Article C', 3),
             (4, 1, 'Article A: Overview', 1),
             (5, 1, 'Article A: Examples', 2)
    `);

    const parentId = null;
    const orderedIds = [2, 1, 3];
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    try {
      await client.query(
        'LOCK TABLE article_order_demo IN SHARE ROW EXCLUSIVE MODE'
      );
      const siblings = await client.query(
        `SELECT id FROM article_order_demo
         WHERE parent_id IS NOT DISTINCT FROM $1::integer`,
        [parentId]
      );
      validateOrder(orderedIds, siblings.rows);
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED'
      );
      for (const [index, id] of orderedIds.entries()) {
        const result = await client.query(
          `UPDATE article_order_demo SET position = $1
           WHERE id = $2
             AND parent_id IS NOT DISTINCT FROM $3::integer`,
          [index + 1, id, parentId]
        );
        if (result.rowCount !== 1) {
          throw new Error('Expected exactly one updated article');
        }
      }
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE'
      );
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    }

    const result = await client.query(
      `SELECT id, title, position FROM article_order_demo
       WHERE parent_id IS NOT DISTINCT FROM $1::integer
       ORDER BY position`,
      [parentId]
    );
    console.table(result.rows);
  } finally {
    if (client) client.release();
    await pool.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

IS NOT DISTINCT FROM 可讓父項篩選條件同時適用於 null 和非 null 值。一般的等號比較搭配 null 參數時，不會選取根文章。若要改為重新排序範例中的子文章，請將 parentId 改為 1，並將 orderedIds 改為 [5, 4]。PostgreSQL：比較函式與運算子

$1 形式的佔位符會將值與 SQL 文字分開傳遞。明確的整數型別轉換也會在父項參數為 null 時，標示其預期型別。node-postgres：查詢

為什麼要在讀取同層文章前取得鎖定？

暫存示範沒有競爭中的工作階段。資料表鎖展示了一種刻意採用的寬鬆做法，適用於小型共用資料表：SHARE ROW EXCLUSIVE 會與一般資料表寫入及自身衝突，但允許一般讀取。在整個交易期間持有該鎖，可讓驗證與更新期間的同層成員保持穩定。它也會阻擋其他父項群組的寫入，因此在忙碌的資料表上使用前，請先評估這項取捨。PostgreSQL：明確鎖定

程式明確選用 READ COMMITTED。在此隔離層級下，同層查詢會看到查詢開始時已提交的變更，包括前一個鎖定等待結束前已提交的變更。PostgreSQL：BEGIN、PostgreSQL：交易隔離

已使用有效排列，以及含重複、缺少和來自其他群組 ID 的情況，檢查 JavaScript 驗證邏輯。這些確定性檢查皆已通過。資料庫範例已依據文件檢視，但未在研究環境中對 PostgreSQL 執行。

PostgreSQL 14 支援哪種替代方案？

在 PostgreSQL 14 中，可將分組運算式實體化為產生欄位，再對這些欄位套用可延後唯一約束。儲存型產生欄位會在寫入資料列時計算，且無法以一般值覆寫。PostgreSQL 14：產生欄位

此獨立 SQL 範例使用兩個產生值表示父項群組。布林值可區分根文章與真正父項 ID 0，因此 COALESCE 預設值不需要保留某個 ID。

CREATE TEMP TABLE article_order_demo14 (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo14(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    parent_is_root boolean
        GENERATED ALWAYS AS (parent_id IS NULL) STORED,
    parent_group integer
        GENERATED ALWAYS AS (COALESCE(parent_id, 0)) STORED,
    CONSTRAINT article_order_demo14_parent_position_key
        UNIQUE (parent_is_root, parent_group, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo14 (id, parent_id, title, position)
VALUES (1, NULL, 'Article A', 1),
       (2, NULL, 'Article B', 2);

BEGIN;
SET CONSTRAINTS article_order_demo14_parent_position_key DEFERRED;
UPDATE article_order_demo14 SET position = 1 WHERE id = 2;
UPDATE article_order_demo14 SET position = 2 WHERE id = 1;
SET CONSTRAINTS article_order_demo14_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo14
WHERE parent_id IS NULL
ORDER BY position;

根文章的產生分組值為 (true, 0)；父項為 0 時，產生值為 (false, 0)。每個非 null 父項都會保留自己的整數值。因此，此約束能表達預期的分組方式，不必依賴 PostgreSQL 15 對 null 的處理方式。PostgreSQL 14 支援對具名欄位建立可延後唯一約束。PostgreSQL 14：CREATE TABLE

修改現有資料表前應檢查什麼？

對 COALESCE(parent_id, 0) 建立運算式索引可以強制執行運算式型唯一性，但無法直接改為可延後，或附加為一般唯一約束。根文章的部分索引也有相同的附加限制。若保留這些即時檢查的物件，新增可延後約束後，交換位置仍可能受阻。PostgreSQL：運算式索引、PostgreSQL：ALTER TABLE

替換現有定義前，請檢查父項與位置的重複配對，尤其是過去因一般 nullable 唯一性規則而獲准存在的根文章。新增唯一約束時會驗證現有資料列；唯一約束不支援 NOT VALID。請先審慎處理不明確的位置，再嘗試變更。PostgreSQL：ALTER TABLE

請將約束所保證的條件與應用程式的排序規則分開考量。唯一性本身允許位置出現 1、4 和 9 這類間隔。完整清單程式會刻意從 1 開始指派連續位置。它的成員資格檢查可確保每篇同層文章都參與排序。

對於此任務，請選用能表示可為 null 父項群組的約束，在第一次可能產生衝突的更新前延後檢查，驗證完整的要求同層集合，並在提交前於同一個用戶端上完成所有更新。這些做法結合後，就能重新排序根文章和子文章，同時確保每個同層群組的最終位置唯一。

來源