PostgreSQLの連番 ― AUTO_INCREMENTの代わりのserialとIDENTITY、番号の振り直し

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

PostgreSQL に AUTO_INCREMENT はありません。書くと構文エラーです。

CREATE TABLE t_ai (id int AUTO_INCREMENT PRIMARY KEY, name text);
ERROR:  syntax error at or near "AUTO_INCREMENT"

連番の列は serial 型か、GENERATED ... AS IDENTITY で作ります。どちらも裏でシーケンスという番号の発行器を作り、そこから番号を取ります。この記事の出力は PostgreSQL 18.6（Ubuntu 26.04 の標準パッケージ）で採りました。

まず見る（結論）

CREATE TABLE t_ident (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text
);
INSERT INTO t_ident (name) VALUES ('a') RETURNING id;

• 新しく作るなら GENERATED ALWAYS AS IDENTITY。番号を手で入れるとエラーになる
• serial は古くからある書き方で、今も使える。番号を手で入れてもエラーにならない
• 入れた番号は RETURNING id で受け取る
• ROLLBACK しても番号は戻らない。連番に欠けが出るのは正常

serial と IDENTITY の違い

CREATE TABLE t_serial (id serial PRIMARY KEY, name text);
CREATE TABLE t_ident  (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text);
CREATE TABLE t_ident2 (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text);

\d で見ると、既定値の欄が違います。

fsv=# \d t_serial
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+--------------------------------------
 id     | integer |           | not null | nextval('t_serial_id_seq'::regclass)

fsv=# \d t_ident
 Column |  Type   | Collation | Nullable |           Default
--------+---------+-----------+----------+------------------------------
 id     | integer |           | not null | generated always as identity

serial は「integer 型の列に、シーケンスから番号を取る既定値を付ける」略記です。型の名前ではないので、\d には integer と出ます。

違いが出るのは、番号を自分で入れたときです。

INSERT INTO t_ident (id, name) VALUES (100, 'x');
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.

UPDATE t_ident SET id = 200 WHERE id = 1;
ERROR:  column "id" can only be updated to DEFAULT

番号を手で入れても、シーケンスには反映されません。BY DEFAULT の表に id = 1 を手で入れた後、番号を省いて INSERT すると、シーケンスが 1 を返して重複しました。

INSERT INTO t_ident2 (id, name) VALUES (1, 'x');
INSERT INTO t_ident2 (name) VALUES ('y');
ERROR:  duplicate key value violates unique constraint "t_ident2_pkey"
DETAIL:  Key (id)=(1) already exists.

ALWAYS なら、番号を手で入れた時点でエラーになります。データ移行などでどうしても番号を指定するときは、INSERT ... OVERRIDING SYSTEM VALUE VALUES (...) と明示します。その後にシーケンスを進めておかないと、上と同じ衝突が起きます（duplicate key の記事）。

入れた番号を受け取る

INSERT INTO t_serial (name) VALUES ('a'), ('b') RETURNING id, name;
 id | name
----+------
  1 | a
  2 | b

RETURNING は INSERT と同じ文の中で値を返すので、他のセッションが同時に INSERT していても取り違えません。MySQL の LAST_INSERT_ID() にあたるのは currval('シーケンス名') と lastval() ですが、どちらも同じセッションで番号を取った後でないと使えません。

SELECT currval('t_serial_id_seq');
ERROR:  currval of sequence "t_serial_id_seq" is not yet defined in this session

シーケンスの名前は 表名_列名_seq になります。確かめるには pg_get_serial_sequence を使います。IDENTITY 列にも使えます。

SELECT pg_get_serial_sequence('t_serial', 'id'), pg_get_serial_sequence('t_ident', 'id');
 pg_get_serial_sequence | pg_get_serial_sequence
------------------------+------------------------
 public.t_serial_id_seq | public.t_ident_id_seq

番号が飛ぶ

BEGIN; INSERT INTO t_serial (name) VALUES ('rolled back'); ROLLBACK;
INSERT INTO t_serial (name) VALUES ('d');
SELECT id, name FROM t_serial ORDER BY id;
 id | name
----+------
  1 | a
  2 | b
  3 | c
  5 | d

4 は ROLLBACK した行が取った番号です。シーケンスは取られた番号を戻さないので、同時に多くのセッションが INSERT しても待ち合わせが起きません。その代わり、ROLLBACK やエラーになった INSERT の分だけ欠けます。伝票番号のように欠けが許されない番号には向きません。

番号を振り直す

RESTART IDENTITY を付けない TRUNCATE は番号を戻しません。RESTART WITH 1000 の後に1行入れると 1000 になります。この表を TRUNCATE t_ident; で空にすると、次の番号は 1001 でした。DELETE で全行消した場合も同じです。

型の上限

上限に達すると INSERT がエラーになります。

SELECT setval('t_small_id_seq', 32767);
INSERT INTO t_small (name) VALUES ('over');
ERROR:  nextval: reached maximum value of sequence "t_small_id_seq" (32767)

ログのように行が増え続ける表は、最初から bigint（bigserial か bigint GENERATED ... AS IDENTITY）にしておきます。後から型を変えると、表を書き直すので大きな表では時間がかかります。

関連: PostgreSQLの「duplicate key value violates unique constraint」 ／ PostgreSQLでテーブル定義を確認する