はじめに
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 ( |
テーブルの状態は以下です。GENERATED AS IDENTITY
を付けると暗黙的にNOT NULL制約がつくことも分かります。
postgres-# \d color; |
このテーブルでcolor_id
を未指定にして、2件データを登録します。
INSERT INTO color (color_name) VALUES ('Orange'); |
結果を見ると、連番が1, 2, …と入っていることが分かります。
postgres=# select * from color; |
自動的に連番が登録される便利機能ですが、実は明示的に値を登録することもできてしまいます。
INSERT INTO color (color_id, color_name) VALUES (3, 'Blue'); |
結果は以下の通り。
postgres=# select * from color; |
一度、明示的にIDENTITY列に値を指定してしまうと、再び未指定でインサートした場合に、重複した値が入り、場合によっては一位制約違反になってしまう可能性があります。これはSERIAL/BIGSERIAL型でも同様のお困りごとでした。
postgres=# INSERT INTO color (color_name) VALUES ('Black'); |
さて、IDENTITY列にはオプションがありまして、 BY DEFAULT
の代わりに ALWAYS
が指定できます。これを利用すると、IDENTITY列に明示的に値を指定することはできなくなります。
DROP TABLE color; |
color_idに3を指定してインサートとしようとするとエラーが出て止められます。
postgres=# INSERT INTO color (color_id, color_name) VALUES (3, 'Blue'); |
脱出ハッチも用意されており、OVERRIDING SYSTEM VALUE
を利用すると強制的に上書きもできます。
INSERT INTO color (color_name) VALUES ('Orange'); |
postgres=# select * from color; |
通常は、 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 を指定 |
結果です。無事登録できています。
postgres=# select * from color; |
どこまでドライバ/ライブラリ/コード生成/解析などのツールが対応しているか不明で、使い所も今イチわかりませんが、チーム内で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); |
テーブルの結果です。
postgres=# select * from color; |
続いて、IDENTITY列に値を指定します。CSVなどからデータ移行する場合はこのようなケースもあるでしょう。こちらも正常終了します(!)。
postgres=# COPY color (color_id, color_name) FROM STDIN WITH (FORMAT csv); |
テーブルの結果です。指定した値でcolor_idが登録されていますね。
postgres=# select * from color; |
COPYの場合、IEDNTITY列
をALTER文で取り除く必要があるかと思いましたが、不要なようです。逆に嬉しいなと感じました。
ドキュメントのどこかに書いていそうだなと探したら、CREATE TABLEやCOPYのページにちゃんと書いてありました。
- https://www.postgresql.jp/document/16/html/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY
- https://www.postgresql.org/docs/current/sql-copy.html#:~:text=For%20identity%20columns
2. シーケンスリセット
単体(E2E)テストなどで、事前/事後データをTRUNCATEして次のテストに備えることはよくあります。この時、TRUNCATEと同時に連番もリセットしたいことが多いでしょう。SERIAL型と同様に、RESET IDENTITY
オプションが利用できます。
-- 何かしらINSERTしてコミット |
参考: https://www.postgresql.jp/docs/16/sql-truncate.html
もちろん、以下のように setval()
でシーケンス値のリセットも可能です。
SELECT setval('color_color_id_seq', 1, false); |
3. パーティションテーブルでの利用
パーティションテーブルで利用可能か、試しています。
-- コメントテーブルを作成 |
テーブル状態は次のようになりました。
\d comment; |
続いて、データ登録します。
INSERT INTO comment (content, comment_date) VALUES ('Orange', '2024-05-15'); |
テーブルは以下のように登録されました。
postgres=# select * from comment; |
さっと利用した感じ、特に課題は無いかなと思います。
4. 作成されたシーケンスの名称
下記のようなSQLで抽出できます。
SELECT |
結果は次の通り、 color_color_id_seq
。{テーブル名}_{カラム名}_seq という体系で生成されるようです。
table_name | column_name | sequence_name |
上記のSQLは少し長いので、 pg_get_serial_sequence(table text, column text)
というシステムカタログ情報関数も用意されています。
SELECT pg_get_serial_sequence('color', 'color_id') AS sequence_name; |
結果です。
sequence_name |
5. 作成されたシーケンスを削除したらどうなるか
誤ってIDENTITY列が内部的に使用するシーケンスオブジェクトを削除したら、不正な状態にならないかテストです。当然、失敗します。
postgres=# drop sequence color_color_id_seq; |
もし、このシーケンスを削除したい場合は、colorテーブルのcolor_id列を削除する必要があるとあります。妥当なメッセージです。
6. シーケンス名の上限63文字を超過したテーブル、カラム名の場合
PostgreSQLではシーケンスに限らず、識別子の最長は63文字です。
そのため、IDENTITYで自動で生成されるシーケンス名の体系が、{テーブル名}_{カラム名}_seq だとすると、超過した場合にどう命名されるか気になりました。
試してみます。テーブル名が36文字、カラム名が39文字です。
CREATE TABLE looooooooooooooooooooooooooooooooong ( |
シーケンス名を確認すると、次の結果となりました。
postgres=# SELECT pg_get_serial_sequence('looooooooooooooooooooooooooooooooong', 'looooooooooooooooooooooooooooooooong_id') AS sequence_name; |
テーブル名、カラム名が長いと最長で29文字で前方からオミットされて生成されるようです。
直接シーケンス名を指定して setval()
するときに困ることが多いかなと思いますので、注意が必要です。
7. テーブル名を変更した時シーケンス名はどうなるか
シーケンス名は自動生成されますが、ALTERでテーブル名を変えた場合にどうなるか確かめます。
ALTER TABLE looooooooooooooooooooooooooooooooong RENAME TO color; |
結果は以下の通り、変化無しです。
postgres=# SELECT pg_get_serial_sequence('color', 'looooooooooooooooooooooooooooooooong_id') AS sequence_name; |
そのため、テーブル名を変更した場合は、シーケンス名もリネームするような運用を行った方が良いでしょう。
8. カラム名をを変更した時シーケンス名はどうなるか
7と同様に、カラム名を変更した場合にシーケンス名がどうなるか確認します。
ALTER TABLE color RENAME COLUMN looooooooooooooooooooooooooooooooong_id TO color_id; |
結果は以下の通り、テーブル名と同様、カラム名の変更も変化ありません。
postgres=# SELECT pg_get_serial_sequence('color', 'color_id') AS sequence_name; |
結論も7と同様、テーブル名/カラム名が変更した場合は、シーケンス名もリネームする運用を行うとベターでしょう。
ALTER SEQUENCE loooooooooooooooooooooooooooo_loooooooooooooooooooooooooooo_seq RENAME TO color_color_id_seq; |
9. 独自に作成したシーケンスとの紐づけ方法
SERIAL型であれば、以下のように指定すると独自のシーケンスと紐づけることができました。
-- 独自シーケンス |
BIGINTを指定していて、BIGSERIALを使っていないじゃない?と思うかもしれません。しかしドキュメントにも記載通り、以下の2つの構文は同義ですので、これが言えます。
CREATE TABLE tablename ( |
CREATE SEQUENCE tablename_colname_seq AS integer; |
IDENTITY列に関しては、手動で作成したシーケンスとIDENTITY列を紐づける構文は、ドキュメントを探した時点では存在しませんでした(予め作成したシーケンスを複数の用途で共有して使いたいといったユースケースは実現できなさそうです)。
名称だけの話であれば可能です。作成時にシーケンスオプションで指定するか、先述の通り、 ALTER SEQUENCE RENAME
で変更できます。
CREATE TABLE color ( |
また、次のように名称以外も、シーケンスオプションで指定できます(ALTERで変更も可能)です。一般的なユースケースでは、困ることは無いかなと思います。
CREATE TABLE color ( |
10. 文字列型とGENERATED AS IDENTITYの組み合わせ
文字列型(text型)にGENERATED ALWAYS AS IDENTITYを指定すると、いい感じの型変換により ‘1’、’2’、…といった採番がされないかと思いついたので試しました。
postgres=# CREATE TABLE color ( |
無事エラーで、これは対応していないようです。型としては、smallint
integer
bigint
のみ対応。
11. SERIAL型とGENERATED AS IDENTITYの組み合わせ
SERIAL型であれば、型としては integer
型なので、いけるのではと一応チャレンジしました。結果は以下のエラーです。
postgres=# CREATE TABLE color ( |
2つIDENTITYが指定されているね、というエラーメッセージです。(当然のことながら)手堅くブロックしてくれています。助かりますね。
まとめ
PostgreSQLの自動採番機能であるIDENTITY列について試しました。すでにSERIAL/BIGSERIAL型を利用している稼働中のシステムであれば、あえて乗り換えるメリットは小さいでしょう。
新規構築分に関しては、ほぼSERIAL/BIGSERIALの使い勝手と同等で、GENERATED ALWAYS
を利用することで誤登録を防ぐことができるという意味で、積極的に利用していく方針で良いのでは?と感じました。
何か他のハマりどころがあれば、Xなどでコメントいただけると幸いです。ありがとうございました。