PostgreSQL連番｜serialとIDENTITYの違いとsetval修正

PostgreSQLで連番を作るには、列を GENERATED ... AS IDENTITY（IDENTITY列）または serial 型にします。どちらも内部では「シーケンス」という採番専用のオブジェクトが番号を払い出します。新しく作るテーブルなら、標準SQLに沿っていて権限や型変更の扱いがシンプルな IDENTITY 列がおすすめです。

連番まわりで多い困りごとは、データ移行やCSV取り込みのあとに出る duplicate key value violates unique constraint です。この記事では、PostgreSQL 16.15でこのエラーを serial と IDENTITY の両方で再現し、setval で直す手順を実行結果つきで示します。あわせて、シーケンス関数の一覧、ロールバックやキャッシュで欠番が出る様子の実測、serial から IDENTITY への移行SQLまでまとめました。

PostgreSQL連番の早見表

serial・IDENTITY・手動シーケンスの違い

serial は「integer列＋シーケンス＋DEFAULT nextval(...)」をまとめて作る省略記法で、IDENTITY は列そのものに採番の性質を持たせる標準SQLの書き方です。 5種類を同じ条件で作り、システムカタログで中身を確認しました。

CREATE TABLE t_serial    (id serial PRIMARY KEY, name text);
CREATE TABLE t_bigserial (id bigserial PRIMARY KEY, name text);
CREATE TABLE t_always    (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text);
CREATE TABLE t_bydefault (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text);
CREATE SEQUENCE manual_seq;
CREATE TABLE t_manual    (id bigint PRIMARY KEY DEFAULT nextval('manual_seq'), name text);

SELECT c.relname AS table_name, format_type(a.atttypid, a.atttypmod) AS type,
       a.attidentity AS identity, pg_get_expr(d.adbin, d.adrelid) AS default_expr,
       pg_get_serial_sequence(c.relname, 'id') AS seq
FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE c.relname IN ('t_serial','t_bigserial','t_always','t_bydefault','t_manual')
  AND a.attname = 'id'
ORDER BY c.relname;

 table_name  |  type   | identity |              default_expr               |            seq
-------------+---------+----------+-----------------------------------------+---------------------------
 t_always    | bigint  | a        |                                         | public.t_always_id_seq
 t_bigserial | bigint  |          | nextval('t_bigserial_id_seq'::regclass) | public.t_bigserial_id_seq
 t_bydefault | bigint  | d        |                                         | public.t_bydefault_id_seq
 t_manual    | bigint  |          | nextval('manual_seq'::regclass)         |
 t_serial    | integer |          | nextval('t_serial_id_seq'::regclass)    | public.t_serial_id_seq

手動で作ったシーケンスは pg_get_serial_sequence で見つかりません（列に「所有」されていないため）。serial の上限は integer の 2,147,483,647、それ以外は bigint の 9,223,372,036,854,775,807 でした（pg_sequences.max_value で確認）。

比較表

どれを選ぶか

IDENTITY列のALWAYSとBY DEFAULTの違い（実行結果）

INSERT INTO t_serial(id, name)    VALUES (100, '明示');   -- serial
INSERT INTO t_bydefault(id, name) VALUES (100, '明示');   -- BY DEFAULT
INSERT INTO t_always(id, name)    VALUES (100, '明示');   -- ALWAYS

INSERT 0 1
INSERT 0 1
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

ALWAYS は、うっかり番号を手で入れてしまうことを防いでくれます。 移行などで意図して入れるときは、HINTのとおり OVERRIDING SYSTEM VALUE を付けます。

INSERT INTO t_always(id, name) OVERRIDING SYSTEM VALUE VALUES (100, '明示');
-- INSERT 0 1
INSERT INTO t_always(name) VALUES ('自動') RETURNING id;

 id
----
  1

100を入れたあとでも、自動採番は1から始まります。 シーケンスは手で入れた値を知らないためです。この性質が、次の章の duplicate key エラーにつながります。

UPDATEも同じように制限されます。

UPDATE t_always SET id = 5 WHERE id = 100;
ERROR:  column "id" can only be updated to DEFAULT
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

UPDATE t_bydefault SET id = 5 WHERE id = 100;
UPDATE 1

逆に、BY DEFAULT 列で「指定された値を無視して採番したい」ときは OVERRIDING USER VALUE を使います。

INSERT INTO tasks(id, title) OVERRIDING USER VALUE VALUES (1, '指定値を無視') RETURNING id;
  id
------
 1001

duplicate keyエラーを再現してsetvalで直す

エラーが起きる流れ

番号付きのデータを INSERT や COPY で直接入れると、テーブルには番号が入りますが、シーケンスは進みません。 そのあと自動採番すると、シーケンスが1から払い出してしまい、既存の番号とぶつかります。

sequenceDiagram
  participant M as 移行データ（id=1〜3）
  participant T as membersテーブル
  participant S as シーケンス（次は1）
  M->>T: COPYでid=1,2,3を投入
  Note over S: シーケンスは動かない
  T->>S: 自動採番の INSERT
  S-->>T: id=1 を払い出す
  T-->>T: id=1 は既にある → duplicate key

serial と IDENTITY（BY DEFAULT）の両方で再現します。

CREATE TABLE members (id serial PRIMARY KEY, name text);
CREATE TABLE tasks   (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title text);

-- members.csv
id,name
1,佐藤
2,鈴木
3,高橋
-- tasks.csv
id,title
1,資料作成
2,備品発注

t107=> \copy members(id,name) FROM 'members.csv' WITH (FORMAT csv, HEADER true)
COPY 3
t107=> \copy tasks(id,title) FROM 'tasks.csv' WITH (FORMAT csv, HEADER true)
COPY 2
t107=> INSERT INTO members(name) VALUES ('新規会員');
ERROR:  duplicate key value violates unique constraint "members_pkey"
DETAIL:  Key (id)=(1) already exists.
t107=> INSERT INTO tasks(title) VALUES ('新規タスク');
ERROR:  duplicate key value violates unique constraint "tasks_pkey"
DETAIL:  Key (id)=(1) already exists.

serial でも IDENTITY でも同じように起きます。 IDENTITY だから自動で合わせてくれる、ということはありません。

状態を確認する

t107=> SELECT max(id) AS max_id,
              (SELECT last_value FROM members_id_seq) AS last_value,
              (SELECT is_called  FROM members_id_seq) AS is_called
       FROM members;
 max_id | last_value | is_called
--------+------------+-----------
      3 |          1 | t

テーブルの最大値は3、シーケンスは1（失敗したINSERTで1を消費済み）。この「最大値＞シーケンスの値」が原因です。 シーケンス名は pg_get_serial_sequence で調べられ、IDENTITY列でも使えます。

t107=> SELECT pg_get_serial_sequence('members','id') AS members_seq,
              pg_get_serial_sequence('tasks','id')   AS tasks_seq;
      members_seq      |      tasks_seq
-----------------------+---------------------
 public.members_id_seq | public.tasks_id_seq

setvalで直す

SELECT setval(pg_get_serial_sequence('members','id'), (SELECT max(id) FROM members));
SELECT setval(pg_get_serial_sequence('tasks','id'),   (SELECT max(id) FROM tasks));

 setval
--------
      3

 setval
--------
      2

t107=> INSERT INTO members(name) VALUES ('新規会員') RETURNING id;
 id
----
  4
t107=> INSERT INTO tasks(title) VALUES ('新規タスク') RETURNING id;
 id
----
  3

シーケンスをテーブルの最大値に合わせると、次の採番は「最大値＋1」になります。 IDENTITY 列でも、シーケンスを直接 setval してかまいません。

空のテーブルで setval するときの注意

t107=> SELECT setval(pg_get_serial_sequence('empty_t','id'), COALESCE((SELECT max(id) FROM empty_t), 0) + 1, false);
 setval
--------
      1
t107=> INSERT INTO empty_t DEFAULT VALUES RETURNING id;
 id
----
  1

データ移行のスクリプトには、空でも動くこの形を入れておくと安心です。

全テーブルのシーケンスをまとめて合わせる

移行後に、public スキーマで serial／IDENTITY を持つ全テーブルを一度に合わせるDOブロックです。

DO $$
DECLARE r record; v bigint;
BEGIN
  FOR r IN
    SELECT n.nspname, c.relname, a.attname,
           pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
    WHERE c.relkind IN ('r','p') AND n.nspname = 'public'
      AND pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) IS NOT NULL
  LOOP
    EXECUTE format('SELECT COALESCE(max(%I), 0) FROM %I.%I', r.attname, r.nspname, r.relname) INTO v;
    IF v > 0 THEN
      PERFORM setval(r.seq, v, true);       -- 次は v+1
    ELSE
      PERFORM setval(r.seq, 1, false);      -- 空なら次は 1
    END IF;
    RAISE NOTICE '% -> %', r.seq, v;
  END LOOP;
END $$;

実行結果（抜粋。わざと members と tasks のシーケンスを1に戻してから実行）：

NOTICE:  public.t_bigserial_id_seq -> 0
NOTICE:  public.empty_t_id_seq -> 1
NOTICE:  public.members_id_seq -> 12
NOTICE:  public.tasks_id_seq -> 1002
NOTICE:  public.articles_id_seq -> 4
DO

手動シーケンス（DEFAULT nextval('共通seq')）は対象外なので、個別に setval します。

シーケンス関数の一覧と実行結果

CREATE SEQUENCE demo_seq;
SELECT currval('demo_seq');
SELECT nextval('demo_seq') AS n1, nextval('demo_seq') AS n2;
SELECT currval('demo_seq'), lastval();
SELECT setval('demo_seq', 10);
SELECT nextval('demo_seq');
SELECT setval('demo_seq', 10, false);
SELECT nextval('demo_seq');
ALTER SEQUENCE demo_seq RESTART;
SELECT nextval('demo_seq');
ALTER SEQUENCE demo_seq RESTART WITH 500;
SELECT nextval('demo_seq');

ERROR:  currval of sequence "demo_seq" is not yet defined in this session
 n1 | n2
----+----
  1 |  2
 currval | lastval
---------+---------
       2 |       2
 setval
--------
     10
 nextval
---------
      11        ← setval(10) の次は 11
 setval
--------
     10
 nextval
---------
      10        ← setval(10, false) の次は 10
 nextval
---------
       1        ← RESTART で開始値へ
 nextval
---------
     500        ← RESTART WITH 500

setval の第3引数 is_called が、つまずきやすい点です。 true（省略時）は「n はもう使った」、false は「n はまだ使っていない」という意味になります。

IDENTITY列は列に対して RESTART できます。

t107=> ALTER TABLE tasks ALTER COLUMN id RESTART WITH 1000;
ALTER TABLE
t107=> INSERT INTO tasks(title) VALUES ('RESTART後') RETURNING id;
  id
------
 1000

採番された番号をアプリで受け取るときは、currval を別に呼ぶより INSERT ... RETURNING id が確実です。1回の往復で済み、どのシーケンスかを意識する必要もありません。

欠番はなぜ出るのか（ロールバック・エラー・キャッシュ）

シーケンスはトランザクションのロールバックで元に戻りません。 同時に多くのセッションが待たずに採番できるよう、そう設計されています。実際に試すと次のようになりました。

BEGIN;
INSERT INTO members(name) VALUES ('取り消す会員') RETURNING id;   -- id = 5
ROLLBACK;
INSERT INTO members(name) VALUES ('次の会員') RETURNING id;       -- id = 6（5は欠番）

INSERT INTO members(name) VALUES ('NG');
ERROR:  new row for relation "members" violates check constraint "members_name_chk"
DETAIL:  Failing row contains (9, NG).                              -- 9を消費
INSERT INTO members(name) VALUES ('OK') RETURNING id;             -- id = 10

INSERT INTO members(name) VALUES ('OK') ON CONFLICT (name) DO NOTHING RETURNING id;
INSERT 0 0                                                          -- 入らないが番号は消費
INSERT INTO members(name) VALUES ('OK2') RETURNING id;            -- id = 12

複数セッションとキャッシュの実測

2つのセッション（A・B）から交互に nextval した結果です（Python + psycopg 3.3.6）。

import psycopg
DSN = "host=127.0.0.1 dbname=t107 user=t107_app password=t107pw"
with psycopg.connect(DSN, autocommit=True) as c:
    c.execute("DROP SEQUENCE IF EXISTS s_nocache, s_cache20")
    c.execute("CREATE SEQUENCE s_nocache")            # CACHE 1（既定）
    c.execute("CREATE SEQUENCE s_cache20 CACHE 20")
a = psycopg.connect(DSN, autocommit=True)   # セッションA
b = psycopg.connect(DSN, autocommit=True)   # セッションB
for seq in ["s_nocache", "s_cache20"]:
    got = []
    for who in ["A", "B", "A", "B", "A"]:
        conn = a if who == "A" else b
        got.append(f"{who}={conn.execute('SELECT nextval(%s)', [seq]).fetchone()[0]}")
    print(f"{seq:10}", " ".join(got))
a.close(); b.close()
with psycopg.connect(DSN) as c:   # 新しいセッション
    print("再接続後 s_cache20:", c.execute("SELECT nextval('s_cache20')").fetchone()[0])

s_nocache  A=1 B=2 A=3 B=4 A=5
s_cache20  A=1 B=21 A=2 B=22 A=3
再接続後 s_cache20: 41

CACHE 20 では、セッションごとに20個ずつ番号をまとめて確保します。 そのため番号は「挿入した順」にならず、セッションが終わると使い残した番号（4〜20、23〜40）は欠番になります。キャッシュは採番の負荷を下げますが、連番の順序や欠番なしを期待する用途には向きません。

欠番を出したくないときは

主キーの連番は「重ならない識別子」と割り切り、欠番があっても困らない設計にしておくと運用が楽になります。

serialからIDENTITYへ移行するSQL

既存の serial 列を IDENTITY に置き換える手順です。トランザクション内で実行し、最後に setval で番号を合わせます。

-- 移行前：id serial
CREATE TABLE articles (id serial PRIMARY KEY, title text);
INSERT INTO articles(title) VALUES ('記事A'), ('記事B'), ('記事C');

BEGIN;
ALTER TABLE articles ALTER COLUMN id DROP DEFAULT;          -- nextval の DEFAULT を外す
DROP SEQUENCE articles_id_seq;                               -- 古いシーケンスを削除
ALTER TABLE articles ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
SELECT setval(pg_get_serial_sequence('articles','id'), (SELECT max(id) FROM articles));
COMMIT;

-- 移行前の \d articles
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+--------------------------------------
 id     | integer |           | not null | nextval('articles_id_seq'::regclass)

-- 移行後の \d articles
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity

t107=> INSERT INTO articles(title) VALUES ('記事D') RETURNING id;
 id
----
  4

新しいシーケンスは同じ名前（articles_id_seq）で作られ、続きの番号から採番されました。 続けて、ALWAYS への変更と bigint 化も行えます。

ALTER TABLE articles ALTER COLUMN id SET GENERATED ALWAYS;
ALTER TABLE articles ALTER COLUMN id TYPE bigint;
SELECT sequencename, data_type, last_value FROM pg_sequences WHERE sequencename = 'articles_id_seq';

  sequencename   | data_type | last_value
-----------------+-----------+------------
 articles_id_seq | bigint    |          4

IDENTITY 列は、列の型を bigint にするとシーケンスの型も bigint に揃います。 移行時の確認ポイントは次のとおりです。

serialで出やすい権限エラー

serial（と手動シーケンス）は、テーブルのINSERT権限に加えて、シーケンスのUSAGE権限が必要です。 INSERT と SELECT だけを付けたロールで試しました。

t107=> GRANT SELECT, INSERT ON members, tasks TO t107_writer;

-- t107_writer で実行
INSERT INTO members(name) VALUES ('権限テスト');        -- members は serial
ERROR:  permission denied for sequence members_id_seq
INSERT INTO tasks(title) VALUES ('権限テスト') RETURNING id;   -- tasks は IDENTITY
  id
------
 1002

serial のテーブルではシーケンスにも権限を付けます。

GRANT USAGE ON SEQUENCE members_id_seq TO t107_writer;
-- これから作るシーケンスにも自動で付ける場合
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO t107_writer;

付与後は INSERT ... RETURNING id が 13 を返し、成功しました。IDENTITY 列ではこの追加の GRANT が不要なのも、新規テーブルで IDENTITY をすすめる理由の1つです。

よくある質問

Q. 欠番を詰め直して1から振り直してもよいですか？ A. 主キーは他のテーブルから外部キーで参照されていることが多く、振り直すと参照先も更新が必要になります。表示用の連番が目的なら、row_number() で付ける方法が手軽です。

Q. pg_dump でバックアップ・リストアしたあとも setval は必要ですか？ A. pg_dump の出力にはシーケンスの現在値を戻す setval が含まれるため、通常は不要です。CSVや別システムからデータを移したときに、この記事の手順で合わせます。

Q. シーケンスが上限に達するとどうなりますか？ A. nextval がエラーになり、INSERT できなくなります。serial（integer）の上限は約21億です。件数が多くなりそうなテーブルは、最初から bigint のIDENTITY（または bigserial）にしておくと安心です。

まとめ

• 新しいテーブルの連番は bigint GENERATED ALWAYS AS IDENTITY がおすすめ
• serial も IDENTITY も中身はシーケンス。 番号付きでINSERT/COPYするとシーケンスは進まない
• duplicate key は setval(pg_get_serial_sequence('t','id'), max(id)) で直る（serial・IDENTITY共通）
• 空テーブルも考えるなら COALESCE(max, 0) + 1, false の形
• setval の第3引数 false は「次にその値を出す」、true（省略時）は「次はその値＋1」
• ロールバック・制約違反・ON CONFLICT・キャッシュで欠番は出る。 欠番なしを前提にしない
• serial はシーケンスのUSAGE権限が必要、IDENTITY は不要。 移行SQLも数行で書ける

株式会社RJCでは、データベース設計の基礎を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。