Movatterモバイル変換

[0]ホーム
URL:

Future Tech Blog
フューチャー技術ブログ
DBカテゴリ

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

はじめに

Technology Innovation Group真野です。

2017/10/5リリースのPostgreSQL 10にて、インサート時に自動で連番を割り当てるGENERATED AS IDENTITY という構文がサポートされました。PostgreSQLの連番作成機能と言えば、SERIALBIGSERIAL 型が有名ですが、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列になります。

CREATETABLE color (
    color_idBIGINT GENERATEDBYDEFAULTASIDENTITYPRIMARY KEY,
    color_nameVARCHARNOTNULL
);

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

postgres-# \d color;
Table "public.color"
Column|       Type|Collation| Nullable|Default
------------+-------------------+-----------+----------+----------------------------------
 color_id|bigint||notnull| generatedbydefaultasidentity
 color_name|charactervarying||notnull|
Indexes:
    "color_pkey"PRIMARY KEY, btree (color_id)

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

INSERTINTO color (color_name)VALUES ('Orange');
INSERTINTO color (color_name)VALUES ('Red');

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

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

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

INSERTINTO color (color_id, color_name)VALUES (3,'Blue');
INSERTINTO 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=#INSERTINTO color (color_name)VALUES ('Black');
ERROR:  duplicate keyvalue violatesuniqueconstraint "color_pkey"
DETAIL:  Key (color_id)=(3) already exists.

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

DROPTABLE color;
CREATETABLE color (
    color_idBIGINT GENERATED ALWAYSASIDENTITYPRIMARY KEY,
    color_nameVARCHARNOTNULL
);

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

postgres=#INSERTINTO color (color_id, color_name)VALUES (3,'Blue');
ERROR:  cannotinsert a non-DEFAULTvalueintocolumn "color_id"
DETAIL:Column "color_id"is anidentitycolumn definedas GENERATED ALWAYS.
HINT:  Use OVERRIDINGSYSTEMVALUEto override.

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

INSERTINTO color (color_name)VALUES ('Orange');
INSERTINTO color (color_name)VALUES ('Red');

-- OVERRIDING SYSTEM VALUEを利用（エラーにせず、IDENTITYに明示的な値を登録可能）
INSERTINTO color (color_id, color_name) OVERRIDINGSYSTEMVALUEVALUES (3,'Blue');
INSERTINTO color (color_id, color_name) OVERRIDINGSYSTEMVALUEVALUES (4,'Brown');
postgres=#select*from color;
 color_id| color_name
----------+------------
1| Orange
2| Red
3| Blue
4| Brown
(4rows)

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

まとめると以下です。

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

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

-- DEFAULT を指定
INSERTINTO 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
(5rows)

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

GENERATED ALWAYS AS IDENTITY に対する調査

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

1. COPY の挙動

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

postgres=#COPY color (color_name)FROM STDINWITH (FORMAT csv);
Enter datato be copied followedby a newline.
Endwith a backslashand aperiodon a lineby itself,or an EOF signal.
>> Orange
>> Red
>> Blue
>> Brown
>> Black
>> \.
>>>>>>>>COPY5

テーブルの結果です。

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

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

postgres=#COPY color (color_id, color_name)FROM STDINWITH (FORMAT csv);
Enter datato be copied followedby a newline.
Endwith a backslashand aperiodon a lineby itself,or an EOF signal.
>>21,Orange
>>22,Red
>>23,Blue
>>24,Brown
>>25,Black
>> \.
COPY5

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

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

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

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

2. シーケンスリセット

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

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

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

-- 別のテストでインサート
INSERTINTO color (color_name)VALUES ('Orange');
INSERTINTO 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. パーティションテーブルでの利用

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

-- コメントテーブルを作成
CREATETABLE comment (
    comment_idBIGINT GENERATED ALWAYSASIDENTITY,
    content TEXTNOTNULL,
    comment_dateDATE,
CONSTRAINT comment_pkPRIMARY KEY (comment_date, comment_id)
)PARTITIONBYRANGE (comment_date);

-- パーティションテーブルの作成
CREATETABLE comment_2024PARTITIONOF comment
FORVALUESFROM ('2024-01-01')TO ('2025-01-01');

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

\d comment;
                     Partitionedtable "public.comment"
Column|  Type|Collation| Nullable|Default
--------------+--------+-----------+----------+------------------------------
 comment_id|bigint||notnull| generated alwaysasidentity
 content| text||notnull|
 comment_date|date||notnull|
Partition key:RANGE (comment_date)
Indexes:
    "comment_pk"PRIMARY KEY, btree (comment_date, comment_id)
Numberof partitions:1 (Use \d+to list them.)

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

INSERTINTO comment (content, comment_date)VALUES ('Orange','2024-05-15');
INSERTINTO comment (content, comment_date)VALUES ('Red','2024-05-16');
INSERTINTO 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
(3rows)

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

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

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

SELECT
    t.relnameas table_name,
    a.attnameas column_name,
    s.relnameas sequence_name
FROM
    pg_class s
JOIN
    pg_depend dON d.objid= s.oid
JOIN
    pg_class tON d.refobjid= t.oid
JOIN
    pg_attribute aON a.attnum= d.refobjsubidAND 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
(1row)

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

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

結果です。

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

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

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

postgres=#drop sequence color_color_id_seq;
ERROR:  cannotdrop sequence color_color_id_seq becausecolumn color_idoftable color requires it
HINT:  You candropcolumn color_idoftable color instead.

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

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

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

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

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

CREATETABLE looooooooooooooooooooooooooooooooong (
    looooooooooooooooooooooooooooooooong_idBIGINT GENERATEDBYDEFAULTASIDENTITYPRIMARY KEY,
    color_nameVARCHARNOTNULL
);

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

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

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

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

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

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

ALTERTABLE looooooooooooooooooooooooooooooooong RENAMETO color;

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

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

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

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

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

ALTERTABLE color RENAMECOLUMN looooooooooooooooooooooooooooooooong_idTO color_id;

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

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

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

シーケンスのリネーム
ALTER SEQUENCE loooooooooooooooooooooooooooo_loooooooooooooooooooooooooooo_seq RENAMETO color_color_id_seq;

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

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

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

-- DEAULTでシーケンスと紐づける
CREATETABLE color (
    color_idBIGINTNOTNULLDEFAULT nextval('custom_color_seq')PRIMARY KEY,
    color_nameVARCHARNOTNULL
);
ALTER SEQUENCE custom_color_seq OWNEDBY color.color_id;

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

CREATETABLE tablename (
    colname SERIAL
);
SERIAL型の裏側
CREATE SEQUENCE tablename_colname_seqASinteger;
CREATETABLE tablename (
    colnameintegerNOTNULLDEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNEDBY tablename.colname;

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

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

シーケンスオプションで指定
CREATETABLE color (
    color_idBIGINT GENERATED ALWAYSASIDENTITY (SEQUENCE NAME custom_color_seq)PRIMARY KEY,
    color_nameVARCHARNOTNULL
);

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

シーケンスの開始値、キャッシュ値などを指定
CREATETABLE color (
    color_idINT GENERATED ALWAYSASIDENTITY (STARTWITH10 INCREMENTBY1 CACHE100),
    color_nameVARCHARNOTNULL
);

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

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

postgres=#CREATETABLE color (
    color_id text GENERATED ALWAYSASIDENTITYPRIMARY KEY,
    color_nameVARCHARNOTNULL
);
ERROR:identitycolumn type must besmallint,integer,orbigint

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

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

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

postgres=#CREATETABLE color (
    color_id SERIAL GENERATEDBYDEFAULTASIDENTITYPRIMARY KEY,
    color_nameVARCHARNOTNULL
);
ERROR:bothdefaultandidentity specifiedforcolumn "color_id"oftable "color"

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

まとめ

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

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

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

目次

  1. はじめに
    1. 記事のサマリ
    2. IDENTITY列の基本
    3. GENERATED ALWAYS AS IDENTITY に対する調査
      1. 1. COPY の挙動
      2. 2. シーケンスリセット
      3. 3. パーティションテーブルでの利用
      4. 4. 作成されたシーケンスの名称
      5. 5. 作成されたシーケンスを削除したらどうなるか
      6. 6. シーケンス名の上限63文字を超過したテーブル、カラム名の場合
      7. 7. テーブル名を変更した時シーケンス名はどうなるか
      8. 8. カラム名をを変更した時シーケンス名はどうなるか
      9. 9. 独自に作成したシーケンスとの紐づけ方法
      10. 10. 文字列型とGENERATED AS IDENTITYの組み合わせ
      11. 11. SERIAL型とGENERATED AS IDENTITYの組み合わせ
    4. まとめ

カテゴリー

[8]ページ先頭
©2009-2025 Movatter.jp