PostgreSQL と H2 Database の両方で使える SEQUENCE 値生成方法 (MySQL の AUTO_INCREMENT 的なやつ)

SQL DB に新しい行を挿入したときに自動的に ID 値を生成して欲しいことはままある。 このような処理をしてくれるテーブル定義についての話。

MySQL だと AUTO_INCREMENT を使いがち

MySQL を使ってる場合は AUTO_INCREMENT で済ませちゃうことが多い (H2 Database でも使用可)。

CREATE TABLE "foo" (
  "id" BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

PostgreSQL のための AUTO_INCREMENT 以外の方法

ただ、PostgreSQL では AUTO_INCREMENT が使えない。 代わりに使える方法を 2 つ書いておく。

BIGSERIAL 型を使う

PostgreSQL で、テーブル定義時に型として BIGSERIAL 型を使用すると内部的に SEQUENCE を利用するようになる。

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

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

PostgreSQL: Documentation: 10: 8.1. Numeric Types

上の例では SERIAL 型が使われているが、これは INTEGER 相当なので、BIGINT 相当のサイズにしたければ BIGSERIAL を使用すること。

ちなみに BIGSERIAL は H2 Database でも使用できる (ちゃんとしたドキュメントは見当たらなかった)。 MySQL でも SERIAL は使用できる (MySQL の場合は SERIAL で BIGINT になる) が、BIGSERIAL は使えない (MySQL 8.0 で検証)。

• 参考 : MySQL :: MySQL 8.0 Reference Manual :: 11.1.1 Numeric Type Overview

外部 SEQUENCE 生成子を使う

標準 SQL に従った方法はこれっぽい。 上の SERIAL 型を使った場合と等価な例に載っているような形になる。 ただ、H2 Database は SEQUENCE の所有カラムを明示できないようなので、そこは指定しないでおく。 (H2 Database でも使いたいので。)

CREATE SEQUENCE "foo_id_seq";
CREATE TABLE "foo" (
    "id" bigint NOT NULL DEFAULT nextval('foo_id_seq')
);

• PostgreSQL : PostgreSQL: Documentation: 10: CREATE SEQUENCE
• H2 Database : SQL Grammar

参考

• i am BEST : MySQL 固有の SERIAL データ型を標準SQL で書き換える（内部シーケンス生成子:IDENTITY） : 本記事では触れていない内部シーケンス生成子の話なども。
• 旧・そーだいなるらくがき帳: MySQL使いの人がPostgreSQLを始めるときの罠をまとめてみた