はじめに

Technology Innovation Group真野です。

2017/10/5リリースのPostgreSQL 10にて、インサート時に自動で連番を割り当てる GENERATED AS IDENTITY という構文がサポートされました。PostgreSQLの連番作成機能と言えば、 SERIAL BIGSERIAL 型が有名ですが、IDENTITYの方がSQL標準準拠です。

SERIALもIDENTITY のどちらも内部的にはシーケンスを利用していますが、IDENTITYの方が手動で連番カラムに値を指定しにくい機能があり（※後述します）、新規の開発案件であれば IDENTITY を利用すると良いでしょう。

その上で IDENTITY に設定したカラムの挙動について不明点があったので調べてみました。最初に基礎情報をまとめ、調査事項の順で説明します。

なお、調査に用いたPostgreSQLバージョンは 17.0 です。

2024/11/13 追記しました:

「作成されたシーケンスの名称」章のシーケンス名取得の方法を追記
「シーケンス名の上限63文字を超過したテーブル、カラム名の場合」章を追加
「テーブル名を変更した時シーケンス名はどうなるか」章を追加
「カラム名をを変更した時シーケンス名はどうなるか」章を追加
「独自に作成したシーケンスとの紐づけ方法」を追加

記事のサマリ

新規構築なら連番の自動採番はSERIAL／BIGSERIALの代わりに GENERATED ALWAYS AS IDENTITY の利用がベター
DEFAULTキーワードは利用せず、省略する
暗黙的に作成されるシーケンスは、テーブル名やカラム名のリネームに追随しないので、合わせてリネームする運用にする
気になった部分の調査事項と結果は下表

調査項目	結果
`COPY` の挙動	IDENTITYを無効なしで実行可能
シーケンスリセット	`RESTART IDENTITY` オプションで可能
パーティションテーブルでの利用	利用できる
作成されたシーケンスの名称	{テーブル名}_{カラム名}_seq
作成されたシーケンスを削除したらどうなるか	削除不可
シーケンス名の上限63文字を超過したテーブル、カラム名の場合	それぞれ29文字上限でオミットされて生成
テーブル名を変更した時シーケンス名はどうなるか	変化なし
カラム名をを変更した時シーケンス名はどうなるか	変化なし
独自に作成したシーケンスとの紐づけ方法	できない
文字列型とGENERATED AS IDENTITYの組み合わせ	設定不可
SERIAL型とGENERATED AS IDENTITYの組み合わせ	設定不可

IDENTITY列の基本

型の後に、 GENERATED BY DEFAULT AS IDENTITY といった構文で指定します。下記でcolor_idがIDENTITY列になります。

CREATE TABLE color (
    color_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    color_name VARCHAR NOT NULL
);

テーブルの状態は以下です。GENERATED AS IDENTITY を付けると暗黙的にNOT NULL制約がつくことも分かります。

postgres-# \d color;
                                   Table "public.color"
   Column   |       Type        | Collation | Nullable |             Default
------------+-------------------+-----------+----------+----------------------------------
 color_id   | bigint            |           | not null | generated by default as identity
 color_name | character varying |           | not null |
Indexes:
    "color_pkey" PRIMARY KEY, btree (color_id)

このテーブルでcolor_id を未指定にして、2件データを登録します。

INSERT INTO color (color_name) VALUES ('Orange');
INSERT INTO color (color_name) VALUES ('Red');

結果を見ると、連番が1, 2, …と入っていることが分かります。

postgres=# select * from color;
 color_id | color_name
----------+------------
        1 | Orange
        2 | Red
(2 rows)

自動的に連番が登録される便利機能ですが、実は明示的に値を登録することもできてしまいます。

INSERT INTO color (color_id, color_name) VALUES (3, 'Blue');
INSERT INTO color (color_id, color_name) VALUES (4, 'Brown');

結果は以下の通り。

postgres=# select * from color;
 color_id | color_name
----------+------------
        1 | Orange
        2 | Red
        3 | Blue
        4 | Brown

一度、明示的にIDENTITY列に値を指定してしまうと、再び未指定でインサートした場合に、重複した値が入り、場合によっては一位制約違反になってしまう可能性があります。これはSERIAL／BIGSERIAL型でも同様のお困りごとでした。

postgres=# INSERT INTO color (color_name) VALUES ('Black');
ERROR:  duplicate key value violates unique constraint "color_pkey"
DETAIL:  Key (color_id)=(3) already exists.

さて、IDENTITY列にはオプションがありまして、 BY DEFAULT の代わりに ALWAYS が指定できます。これを利用すると、IDENTITY列に明示的に値を指定することはできなくなります。

DROP TABLE color;
CREATE TABLE color (
    color_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    color_name VARCHAR NOT NULL
);

color_idに3を指定してインサートとしようとするとエラーが出て止められます。

postgres=# INSERT INTO color (color_id, color_name) VALUES (3, 'Blue');
ERROR:  cannot insert a non-DEFAULT value into column "color_id"
DETAIL:  Column "color_id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

脱出ハッチも用意されており、OVERRIDING SYSTEM VALUE を利用すると強制的に上書きもできます。

INSERT INTO color (color_name) VALUES ('Orange');
INSERT INTO color (color_name) VALUES ('Red');

-- OVERRIDING SYSTEM VALUEを利用（エラーにせず、IDENTITYに明示的な値を登録可能）
INSERT INTO color (color_id, color_name) OVERRIDING SYSTEM VALUE VALUES (3, 'Blue');
INSERT INTO color (color_id, color_name) OVERRIDING SYSTEM VALUE VALUES (4, 'Brown');

postgres=# select * from color;
 color_id | color_name
----------+------------
        1 | Orange
        2 | Red
        3 | Blue
        4 | Brown
(4 rows)

通常は、 OVERRIDING SYSTEM VALUE をうっかり付けて登録してしまう開発者はごく限られていると想定すると、GENERATED BY DEFAULT AS IDENTITY をSERIAL型の代わりに利用する方が、誤登録を発生させずベターだと思います。

まとめると以下です。

項目	説明
GENERATED BY DEFAULT AS IDENTITY	SERIAL型と同等。自動採番列に登録可能
GENERATED ALWAYS AS IDENTITY	SERIALと同等だが、`OVERRIDING SYSTEM VALUE`を付けないことには登録不可

余談ですが、該当カラムに明示的にインサートしていることを示しつつ、値が自動採番を用いることを示したい場合は DEFAULT キーワードを利用することもできます。

-- DEFAULT を指定
INSERT INTO color (color_id, color_name) VALUES (DEFAULT, 'Black');

結果です。無事登録できています。

postgres=# select * from color;
 color_id | color_name
----------+------------
        1 | Orange
        2 | Red
        3 | Blue
        4 | Brown
        5 | Black
(5 rows)

どこまでドライバ／ライブラリ／コード生成／解析などのツールが対応しているか不明で、使い所も今イチわかりませんが、チーム内でIDENTITY列にインサートする際、「省略する／DEFAULTを指定する」のどちらかは統一したほうが良いでしょう。私は省略で良いかなと思いますが、みなさんはどうお考えでしょうか？

GENERATED ALWAYS AS IDENTITY に対する調査

前章の通り、自動連番生成列だと GENERATED ALWAYS AS IDENTITY がベターな選択だという前提で、以下を調査しました。

1. COPY の挙動

データ移行などで大量のデータ登録に COPY を用いることが多いでしょう。まずIDENTITY列が未指定の場合で動かします。FROMに STDIN を指定することで標準入力で動かすことができるので、これで検証します。最後にCOPY 5とあり、正常終了したことがわかります。

postgres=# COPY color (color_name) FROM STDIN WITH (FORMAT csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> Orange
>> Red
>> Blue
>> Brown
>> Black
>> \.
>> >> >> >> COPY 5

テーブルの結果です。

postgres=# select * from color;
 color_id | color_name
----------+------------
        1 | Orange
        2 | Red
        3 | Blue
        4 | Brown
        5 | Black
(5 rows)

続いて、IDENTITY列に値を指定します。CSVなどからデータ移行する場合はこのようなケースもあるでしょう。こちらも正常終了します（！）。

postgres=# COPY color (color_id, color_name) FROM STDIN WITH (FORMAT csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 21,Orange
>> 22,Red
>> 23,Blue
>> 24,Brown
>> 25,Black
>> \.
COPY 5

テーブルの結果です。指定した値でcolor_idが登録されていますね。

postgres=# select * from color;
 color_id | color_name
----------+------------
       21 | Orange
       22 | Red
       23 | Blue
       24 | Brown
       25 | Black
(5 rows)

COPYの場合、IEDNTITY列 をALTER文で取り除く必要があるかと思いましたが、不要なようです。逆に嬉しいなと感じました。

ドキュメントのどこかに書いていそうだなと探したら、CREATE TABLEやCOPYのページにちゃんと書いてありました。

2. シーケンスリセット

単体（E2E）テストなどで、事前／事後データをTRUNCATEして次のテストに備えることはよくあります。この時、TRUNCATEと同時に連番もリセットしたいことが多いでしょう。SERIAL型と同様に、RESET IDENTITY オプションが利用できます。

-- 何かしらINSERTしてコミット

-- RESTART IDENTITY オプションでシーケンスもリセット
TRUNCATE TABLE color RESTART IDENTITY;

-- 別のテストでインサート
INSERT INTO color (color_name) VALUES ('Orange');
INSERT INTO color (color_name) VALUES ('Red')

-- 連番はリセットされ1から始まる
 color_id | color_name
----------+------------
        1 | Orange
        2 | Red

参考: https://www.postgresql.jp/docs/16/sql-truncate.html

もちろん、以下のように setval()でシーケンス値のリセットも可能です。

SELECT setval('color_color_id_seq', 1, false);

3. パーティションテーブルでの利用

パーティションテーブルで利用可能か、試しています。

-- コメントテーブルを作成
CREATE TABLE comment (
    comment_id BIGINT GENERATED ALWAYS AS IDENTITY,
    content TEXT NOT NULL,
    comment_date DATE,
    CONSTRAINT comment_pk PRIMARY KEY (comment_date, comment_id)
) PARTITION BY RANGE (comment_date);

-- パーティションテーブルの作成
CREATE TABLE comment_2024 PARTITION OF comment
  FOR VALUES  FROM ('2024-01-01') TO ('2025-01-01');

テーブル状態は次のようになりました。

\d comment;
                     Partitioned table "public.comment"
    Column    |  Type  | Collation | Nullable |           Default
--------------+--------+-----------+----------+------------------------------
 comment_id   | bigint |           | not null | generated always as identity
 content      | text   |           | not null |
 comment_date | date   |           | not null |
Partition key: RANGE (comment_date)
Indexes:
    "comment_pk" PRIMARY KEY, btree (comment_date, comment_id)
Number of partitions: 1 (Use \d+ to list them.)

続いて、データ登録します。

INSERT INTO comment (content, comment_date) VALUES ('Orange', '2024-05-15');
INSERT INTO comment (content, comment_date) VALUES ('Red', '2024-05-16');
INSERT INTO comment (content, comment_date) VALUES ('Blue', '2024-05-16');

テーブルは以下のように登録されました。

postgres=# select * from comment;
 comment_id | content | comment_date
------------+---------+--------------
          1 | Orange  | 2024-05-15
          2 | Red     | 2024-05-16
          3 | Blue    | 2024-05-16
(3 rows)

さっと利用した感じ、特に課題は無いかなと思います。

4. 作成されたシーケンスの名称

下記のようなSQLで抽出できます。

 SELECT
    t.relname as table_name,
    a.attname as column_name,
    s.relname as sequence_name
FROM
    pg_class s
JOIN
    pg_depend d ON d.objid = s.oid
JOIN
    pg_class t ON d.refobjid = t.oid
JOIN
    pg_attribute a ON a.attnum = d.refobjsubid AND a.attrelid = t.oid
WHERE
    s.relkind = 'S'
;

結果は次の通り、 color_color_id_seq。{テーブル名}_{カラム名}_seq という体系で生成されるようです。

 table_name | column_name |   sequence_name
------------+-------------+--------------------
 color      | color_id    | color_color_id_seq
(1 row)

上記のSQLは少し長いので、 pg_get_serial_sequence(table text, column text) というシステムカタログ情報関数も用意されています。

https://www.postgresql.jp/document/16/html/functions-info.html#FUNCTIONS-INFO-CATALOG

SELECT pg_get_serial_sequence('color', 'color_id') AS sequence_name;

結果です。

       sequence_name
---------------------------
 public.color_color_id_seq
(1 row)

5. 作成されたシーケンスを削除したらどうなるか

誤ってIDENTITY列が内部的に使用するシーケンスオブジェクトを削除したら、不正な状態にならないかテストです。当然、失敗します。

postgres=#  drop sequence color_color_id_seq;
ERROR:  cannot drop sequence color_color_id_seq because column color_id of table color requires it
HINT:  You can drop column color_id of table color instead.

もし、このシーケンスを削除したい場合は、colorテーブルのcolor_id列を削除する必要があるとあります。親切なメッセージですね。

6. シーケンス名の上限63文字を超過したテーブル、カラム名の場合

PostgreSQLではシーケンスに限らず、識別子の最長は63文字です。

https://www.postgresql.jp/document/16/html/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

そのため、IDENTITYで自動で生成されるシーケンス名の体系が、{テーブル名}_{カラム名}_seq だとすると、超過した場合にどう命名されるか気になりました。

試してみます。テーブル名が36文字、カラム名が39文字です。

CREATE TABLE looooooooooooooooooooooooooooooooong (
    looooooooooooooooooooooooooooooooong_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    color_name VARCHAR NOT NULL
);

シーケンス名を確認すると、次の結果となりました。

postgres=# SELECT pg_get_serial_sequence('looooooooooooooooooooooooooooooooong', 'looooooooooooooooooooooooooooooooong_id') AS sequence_name;
                             sequence_name
------------------------------------------------------------------------
 public.loooooooooooooooooooooooooooo_loooooooooooooooooooooooooooo_seq

テーブル名、カラム名が長いと最長で29文字で前方からオミットされて生成されるようです。エラーにならない！ 点は注意が必要です。

直接シーケンス名を指定して setval() するときに困ることが多いかなと思いますので、注意が必要です。

7. テーブル名を変更した時シーケンス名はどうなるか

シーケンス名は自動生成されますが、ALTERでテーブル名を変えた場合にどうなるか確かめます。

ALTER TABLE looooooooooooooooooooooooooooooooong RENAME TO color;

結果は以下の通り、変化無しです。

postgres=# SELECT pg_get_serial_sequence('color', 'looooooooooooooooooooooooooooooooong_id') AS sequence_name;
                             sequence_name
------------------------------------------------------------------------
 public.loooooooooooooooooooooooooooo_loooooooooooooooooooooooooooo_seq
(1 row)

そのため、テーブル名を変更した場合は、シーケンス名もリネームするような運用を行った方が良いでしょう。

8. カラム名をを変更した時シーケンス名はどうなるか

7と同様に、カラム名を変更した場合にシーケンス名がどうなるか確認します。

ALTER TABLE color RENAME COLUMN looooooooooooooooooooooooooooooooong_id TO color_id;

結果は以下の通り、テーブル名と同様、カラム名の変更も変化ありません。

postgres=# SELECT pg_get_serial_sequence('color', 'color_id') AS sequence_name;
                             sequence_name
------------------------------------------------------------------------
 public.loooooooooooooooooooooooooooo_loooooooooooooooooooooooooooo_seq
(1 row)

結論も7と同様、テーブル名／カラム名が変更した場合は、シーケンス名もリネームする運用を行うとベターでしょう。

シーケンスのリネーム

ALTER SEQUENCE loooooooooooooooooooooooooooo_loooooooooooooooooooooooooooo_seq RENAME TO color_color_id_seq;

9. 独自に作成したシーケンスとの紐づけ方法

SERIAL型であれば、以下のように指定すると独自のシーケンスと紐づけることができました。

-- 独自シーケンス
CREATE SEQUENCE custom_color_seq;

-- DEAULTでシーケンスと紐づける
CREATE TABLE color (
    color_id BIGINT NOT NULL DEFAULT nextval('custom_color_seq') PRIMARY KEY,
    color_name VARCHAR NOT NULL
);
ALTER SEQUENCE custom_color_seq OWNED BY color.color_id;

BIGINTを指定していて、BIGSERIALを使っていないじゃない？と思うかもしれません。しかしドキュメントにも記載通り、以下の2つの構文は同義ですので、これが言えます。

CREATE TABLE tablename (
    colname SERIAL
);

SERIAL型の裏側

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

IDENTITY列に関しては、手動で作成したシーケンスとIDENTITY列を紐づける構文は、ドキュメントを探した時点では存在しませんでした（予め作成したシーケンスを複数の用途で共有して使いたいといったユースケースは実現できなさそうです）。

名称だけの話であれば可能です。作成時にシーケンスオプションで指定するか、先述の通り、 ALTER SEQUENCE RENAME で変更できます。

シーケンスオプションで指定

CREATE TABLE color (
    color_id BIGINT GENERATED ALWAYS AS IDENTITY (SEQUENCE NAME custom_color_seq) PRIMARY KEY,
    color_name VARCHAR NOT NULL
);

また、次のように名称以外も、シーケンスオプションで指定できます（ALTERで変更も可能）です。一般的なユースケースでは、困ることは無いかなと思います。

シーケンスの開始値、キャッシュ値などを指定

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1 CACHE 100),
    color_name VARCHAR NOT NULL
);

10. 文字列型とGENERATED AS IDENTITYの組み合わせ

文字列型（text型）にGENERATED ALWAYS AS IDENTITYを指定すると、いい感じの型変換により ‘1’、’2’、…といった採番がされないかと思いついたので試しました。

postgres=# CREATE TABLE color (
    color_id text GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    color_name VARCHAR NOT NULL
);
ERROR:  identity column type must be smallint, integer, or bigint

無事エラーで、これは対応していないようです。型としては、smallint integer bigint のみ対応。

11. SERIAL型とGENERATED AS IDENTITYの組み合わせ

SERIAL型であれば、型としては integer 型なので、いけるのではと一応チャレンジしました。結果は以下のエラーです。

postgres=# CREATE TABLE color (
    color_id SERIAL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    color_name VARCHAR NOT NULL
);
ERROR:  both default and identity specified for column "color_id" of table "color"

2つIDENTITYが指定されているね、というエラーメッセージです。（当然のことながら）手堅くブロックしてくれています。助かりますね。

まとめ

PostgreSQLの自動採番機能であるIDENTITY列について試しました。すでにSERIAL／BIGSERIAL型を利用している稼働中のシステムであれば、あえて乗り換えるメリットは小さいでしょう。

新規構築分に関しては、ほぼSERIAL/BIGSERIALの使い勝手と同等で、GENERATED ALWAYS を利用することで誤登録を防ぐことができるという意味で、積極的に利用していく方針で良いのでは？と感じました。

何か他のハマりどころがあれば、Xなどでコメントいただけると幸いです。ありがとうございました。

PostgreSQLで連番を自動生成するIDENTITY列。SERIALとどちらを使うべきか