PostgreSQL 15以降では、UNIQUE NULLS NOT DISTINCT制約をDEFERRABLEとして宣言し、並べ替えトランザクション内でその制約を遅延させます。これにより、親がNULLの行も含め、最終的な順序の一意性を保ちながら記事行の位置を入れ替えられます。PostgreSQLの制約構文

このチュートリアルは、1つの親グループ内で合成記事行を並べ替える必要がある、Mac Terminalを使う開発者向けです。SQLのデモ、要求された順序を検証するnode-postgresの完全なプログラム、PostgreSQL 14での代替方法を紹介します。記事の親を変更する処理は対象外です。

NULL可能な親があると、位置の一意性が難しくなるのはなぜですか？

各記事がid、NULL可能なparent_id、正の整数positionを持つとします。同じ親を持つ記事は、1つの順序付き兄弟グループを構成します。このモデルでは、親がNULLの記事はすべて1つのルートグループに属します。

通常のUNIQUE (parent_id, position)では、そのルートグループのルールを適用できません。PostgreSQLは通常、一意性の判定でNULLを互いに異なる値として扱うため、2つのルート記事がどちらも位置1を占められます。NULLS NOT DISTINCTを加えると、複合キーを検査する際に、これらのNULLの親値を同じものとして比較します。NULL以外の親ごとに、位置1をそれぞれ持てます。PostgreSQL：一意制約

並べ替えでは、別のタイミング上の問題も生じます。記事1が位置1を、記事2が位置2を占めているとき、記事2を位置1へ移動すると、一時的に記事1の位置と重複します。遅延不可の一意制約は、挿入または変更された各行を直ちに検査します。遅延可能な制約は、文の終了時、または明示的に遅延した場合はトランザクションの終了時まで検査を待てます。PostgreSQL：CREATE TABLE

複数のUPDATE文で並べ替える場合、この2つの性質がどちらも重要です。NULLを考慮した一意性によって正しいグループが定まり、検査を遅延させることで、更新の途中で一時的に位置が重複しても処理できます。

PostgreSQLのバージョンに応じて、どの定義を使えばよいですか？

PostgreSQL 15では、一意制約と一意インデックスにNULLS NOT DISTINCTが導入されました。以下の主な例はサーバーバージョン15以降が必要です。PostgreSQL 14では、後述する代替方法を使います。インストール済みのコマンドラインクライアントのバージョンから推測せず、接続先サーバーのバージョンを確認してください。PostgreSQL 15のリリースノート

状況 対応方法
PostgreSQL 15以降で新しいテーブルを作成する DEFERRABLE INITIALLY IMMEDIATEを指定した、名前付きのUNIQUE NULLS NOT DISTINCT制約
既存の通常の一意制約 定義を置き換える。ALTER CONSTRAINTでは、一意制約の遅延可能性を変更できない
既存の式インデックスまたは部分一意インデックス 制約の適用方法を置き換える。通常の一意制約として関連付けることはできない
PostgreSQL 14 生成列でグループ分けし、遅延可能な一意制約を使う

既存オブジェクトに関する制限はALTER TABLEのドキュメントに記載されています。この方法で制約属性を変更できるのは外部キーに限られます。また、一意制約として関連付けるインデックスには、式を含めたり部分インデックスにしたりできません。単独のCREATE UNIQUE INDEXにもDEFERRABLE句はありません。PostgreSQL：ALTER TABLE、PostgreSQL：CREATE INDEX

SQLで2つのルート記事を入れ替えるには？

前提条件は、既存のローカル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;

最初のUPDATEの後、トランザクション内では2つのルート記事が一時的に位置1を占めます。2つ目のUPDATEの後、再び位置が一意になります。制約をIMMEDIATEに戻すと、その時点で保留中の変更が検査されます。重複が残っていれば、そのコマンドは失敗します。重複がなければトランザクションをコミットできます。遅延によって変わるのは検証のタイミングです。PostgreSQL：SET CONSTRAINTS

ルート記事の最終的な順序は次のとおりです。

位置 記事ID タイトル
1 2 記事B
2 1 記事A
3 3 記事C

どちらのUPDATEも子記事を選択しないため、子記事の位置は変わりません。対話型トランザクションでエラーが発生した場合は、再試行する前に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：トランザクション分離

JavaScriptの検証では、有効な順列と、重複、欠落、別グループのIDを使って確認しました。これらの決定的なチェックは成功しました。データベースの例はドキュメントと照合しましたが、調査環境ではPostgreSQLに対して実行していません。

PostgreSQL 14で利用できる代替方法は？

PostgreSQL 14では、グループ化式を生成列として実体化し、それらの列に遅延可能な一意制約を適用します。保存された生成列は行の書き込み時に計算され、通常の値で上書きすることはできません。PostgreSQL 14：生成列

次の単独のSQL例では、親グループを表すために2つの生成値を使います。ブール値によってルートと親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

既存の定義を置き換える前に、親と位置のペアに重複がないか確認してください。通常のNULL可能な一意性制約では許されていたルートは、特に注意が必要です。一意制約を追加すると既存の行も検証されます。一意制約ではNOT VALIDを使えません。変更に着手する前に、曖昧な位置を意図的に解消してください。PostgreSQL：ALTER TABLE

制約が保証する内容と、アプリケーションの順序ルールは分けて考えてください。一意性だけでは、位置1、4、9のような間隔を許します。完全なリストを使うプログラムは、連続する位置を1から割り当てます。すべての兄弟が並べ替えに含まれることは、メンバーシップ検証によって保証されます。

この作業では、NULL可能な親グループを表す制約を選び、最初に重複が生じるUPDATEの前に遅延させ、要求された兄弟集合全体を検証し、同じクライアントですべてのUPDATEを終えてからコミットします。この組み合わせにより、ルートと子の並べ替えをサポートしながら、各兄弟の最終位置の一意性を保てます。

参考資料