【PostgreSQL】シーケンスとSERIAL・IDENTITY列の違い

PostgreSQL
2023.01.24

こんにちは、かつコーチです。

PostgreSQLで主キーを自動採番する方法を調べると、SERIALとGENERATED ... AS IDENTITYという2つの書き方が出てきて、どちらを使えばいいか迷った方も多いのではないでしょうか。

結論から言うと、新しくテーブルを作るならIDENTITY列を使うのが現在の推奨です。

今回は、土台となるシーケンスの仕組みから、SERIALとIDENTITYの違い、移行時の注意点まで整理します。

シーケンスとは？

用語の定義：シーケンス（sequence）

シーケンスとは、呼び出すたびに値が増えていく（または減っていく）連番を生成する、PostgreSQLの独立したオブジェクトです。

テーブルとは別に作成・管理でき、単体で使うこともできます。

CREATE SEQUENCE order_number_seq START 1000 INCREMENT 1;

SELECT nextval('order_number_seq'); -- 1000
SELECT nextval('order_number_seq'); -- 1001
SELECT currval('order_number_seq'); -- 1001（直前に取得した値）

STARTで開始値、INCREMENTで増分を指定できます。

主キーの自動採番は、このシーケンスをテーブルの列に紐づけて実現しています。

なぜシーケンスの仕組みを知る必要があるのか

SERIALやIDENTITYは便利な省略記法ですが、内部的にはシーケンスを1つ作成して列のデフォルト値に紐づけているだけです。

仕組みを理解していないと、「欠番が出るのはバグでは」「シーケンスの値をリセットしたい」といった場面で対処に困ります。

SERIALとIDENTITYの違い

SERIALの書き方と正体

SERIALは、シーケンスの作成と列への紐づけをまとめて行う糖衣構文（シンタックスシュガー）です。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL
);

この1文は、内部的には次の処理と同等です。

CREATE SEQUENCE orders_order_id_seq;

CREATE TABLE orders (
    order_id INTEGER NOT NULL DEFAULT nextval('orders_order_id_seq') PRIMARY KEY,
    customer_id INT NOT NULL
);

ALTER SEQUENCE orders_order_id_seq OWNED BY orders.order_id;

order_id列の実体は「シーケンスの値をデフォルト値として使う、ただのINTEGER列」ということです。

IDENTITY列の書き方

PostgreSQL 10で標準SQL準拠の書き方として導入されたのがGENERATED ... AS IDENTITYです。

CREATE TABLE orders (
    order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL
);

GENERATED ALWAYS AS IDENTITYにすると、原則としてINSERT時にorder_idを明示的に指定できなくなります。

意図的に値を指定したい場合（データ移行時など）はOVERRIDING SYSTEM VALUEを付ける必要があります。

INSERT INTO orders (order_id, customer_id)
OVERRIDING SYSTEM VALUE
VALUES (9999, 42);

一方GENERATED BY DEFAULT AS IDENTITYにすると、SERIALと同じように明示的な値指定を制限なく許可します。

CREATE TABLE orders (
    order_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL
);

なぜIDENTITYが推奨されるのか

SERIALは列の型が実質「シーケンス連動のINTEGER」であることが分かりにくく、権限管理にも癖があります。

SERIAL列で採番専用シーケンスへの直接操作権限をユーザーに与えるには、テーブルとは別にGRANTをシーケンスへ発行する必要があり、うっかり漏れることがあります。

IDENTITYはテーブルの権限管理と統合されており、テーブルへのINSERT権限があれば採番も含めて完結します。

また、標準SQLに準拠した書き方であるため、他のRDBMS（Oracle、SQL Serverなど）からの移行者にも馴染みやすいという利点もあります。

PostgreSQL公式ドキュメントでも、新規開発ではSERIALではなくIDENTITY列の使用が案内されています。

よくあるつまずきポイント・エラー対処

Before/After：ROLLBACKしても欠番が埋まらない

シーケンスはトランザクション（一連の処理をひとまとまりとして扱う仕組み）の外で値を発行するため、ROLLBACKしても消費済みの番号は戻りません。

-- ❌ Before：欠番が出ないと思い込んで実装していた
BEGIN;
INSERT INTO orders (customer_id) VALUES (1); -- order_id = 105が採番される
ROLLBACK;

INSERT INTO orders (customer_id) VALUES (2); -- order_id = 106（105は欠番のまま）

私が新人だった頃、注文番号が飛び飛びになる現象を「バグでは」と1時間ほど調べて、結局これが仕様だと気づいた経験があります。

シーケンスがトランザクションをまたいで独立している設計は、複数セッションが同時に採番しても衝突しないようにするためのものです。

-- ✅ After：欠番が出ても業務上問題ない前提で設計する、
-- 連番の抜けが許されない場合はシーケンスに頼らず別途採番テーブル＋ロックで管理する
CREATE TABLE invoice_numbers (
    year INT PRIMARY KEY,
    last_number INT NOT NULL DEFAULT 0
);

-- 請求書番号のように「欠番厳禁」の採番はSELECT ... FOR UPDATEで排他制御する
BEGIN;
SELECT last_number FROM invoice_numbers WHERE year = 2026 FOR UPDATE;
UPDATE invoice_numbers SET last_number = last_number + 1 WHERE year = 2026;
COMMIT;

一般的な主キーの採番は欠番が出ても問題ありませんが、請求書番号のように法的に連番が求められる場面では、シーケンスではなく別のロック機構を使う必要があります。

シーケンスの現在値と実データがずれる

pg_dumpでデータを復元した際などに、シーケンスの現在値がテーブルの最大IDより小さいままになり、主キー重複エラーが出ることがあります。

-- 実データの最大値に合わせてシーケンスを補正する
SELECT setval(
    pg_get_serial_sequence('orders', 'order_id'),
    (SELECT COALESCE(MAX(order_id), 1) FROM orders)
);

pg_get_serial_sequence関数を使えば、列名からシーケンス名をハードコーディングせずに取得できるので、SERIAL・IDENTITYどちらでも使えます。

応用・一歩先の使い方

既存のSERIAL列をIDENTITY列に移行する

既存プロジェクトをSERIALからIDENTITYへ移行したい場合、テーブルを作り直さずに変換できます。

-- 1. 既存のデフォルト値（シーケンス紐づけ）を外す
ALTER TABLE orders ALTER COLUMN order_id DROP DEFAULT;

-- 2. 既存シーケンスをIDENTITY定義に組み込む
ALTER TABLE orders ALTER COLUMN order_id
ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 1000);

-- 3. 既存データの最大値に採番開始位置を合わせる
SELECT setval(
    pg_get_serial_sequence('orders', 'order_id'),
    (SELECT MAX(order_id) FROM orders)
);

既存のorders_order_id_seq自体はSERIAL作成時のまま残っているため、参照権限まわりの整理は別途必要になる点に注意してください。

まとめ

この記事のポイント

  • シーケンスは独立した連番オブジェクトで、SERIALもIDENTITYもその上に成り立つ
  • SERIALは列の実体が分かりにくく、権限管理も別管理になりがち
  • IDENTITYは標準SQL準拠で、テーブル権限と統合されており新規開発では推奨
  • シーケンスはトランザクションの外で採番するため、ROLLBACKしても欠番は埋まらない
  • 連番の抜けが許されない用途（請求書番号など）は、シーケンスではなく行ロックで別管理する

次に読むべき記事

設計編はここで一区切りです。

次はパフォーマンス面から、検索を高速化するインデックスの種類を見ていきましょう。

  • スキーマを使ってテーブルを整理する
  • PostgreSQLのインデックスの種類（B-tree・GiST・GIN・BRIN）（近日公開）

タグ: PostgreSQL, 中級者向け, 設計

タイトルとURLをコピーしました