GENERATED ALWAYS/BY DEFAULT を設定する(連続した数値を自動でカラムに格納する)

テーブルを作成する時にカラムに対して GENERATED ALWAYS AS IDENTITY または GENERATED BY DEFAULT AS IDENTITY をつけると、カラムに自動的に連続した数値を格納することができます。ここでは PostgreSQL でカラムに GENERATED ALWAYS/BY DEFAULT を設定する方法について解説します。

※ カラムに対するデータ型として serial などの連番型を設定した場合も同じようなことができます。詳しくは「連番型」を参照されてください。

(Last modified: 2023年01月07日)

GENERATED ALWAYS/BY DEFAULTの使い方

テーブルを作成するとき、カラムに対して GENERATED ALWAYS AS IDENTITY または GENERATED BY DEFAULT AS IDENTITY を設定するとカラムに自動て連番の数値を格納することができます書式は次のとおりです。

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [, ... ]
)

どちらを設定してもカラムに対して自動的に連続した数値が格納されますが、 GENERATED ALWAYS を設定すると対象となるカラムに値を指定してデータを追加した場合エラーとなりデータが追加できません。逆に GENERATED BY DEFAULT を設定すると対象となるカラムに値を指定してデータを追加した場合、指定した値の方が優先されてデータが追加されます。

GENERATED ALWAYS AS IDENTITY

それでは実際に試してみます。最初に GENERATED ALWAYS AS IDENTITY を設定してみます。 mydb データベースの myschema スキーマの中に次のようなテーブルを作成しました。

create table myschema.friends (
  id integer generated always as identity, 
  name varchar(10)
);

今回 id カラムに対して GENERATED ALWAYS AS IDENTITY を設定しています。

psql メタコマンドの \d コマンドを使って作成したテーブルのカラムの情報を取得してみます。

id カラムの「デフォルト」列に generated always as identity と表示されています。

それでは id カラムには値を指定せずにデータを 3 つ追加してみます。

insert into myschema.friends (name) values 
  ('Yamada'), 
  ('Suzuki'), 
  ('Katou');

データを追加したあとにテーブルからデータを取得してみると、 id カラムには自動的に 1, 2, 3 と連続した数値が格納されています。

今度は id カラムにも値を指定してデータを追加してみます。

insert into myschema.friends values (10, 'Hoshino');

GENERATED ALWAYS AS IDENTITY が設定されたカラムに値を指定してデータを追加しようとすると ERROR: 列"id"への挿入はできません とエラーが表示されてデータの追加が失敗しました。

もし GENERATED ALWAYS AS IDENTITY が設定されたカラムに値を指定してデータを追加したい場合には、 INSERT コマンドを実行する時に OVERRIDING SYSTEM VALUE をつけて実行してください。

insert into myschema.friends overriding system value
  values (10, 'Hoshino');

今度はエラーにならずにデータを追加することができました。追加されたデータの id カラムには指定した値が格納されました。

GENERATED BY DEFAULT AS IDENTITY

次に GENERATED BY DEFAULT AS IDENTITY を設定してみます。 mydb データベースの myschema スキーマの中に次のようなテーブルを作成しました。

create table myschema.branchlist (
  id integer generated by default as identity, 
  name varchar(10)
);

今回 id カラムに対して GENERATED BY DEFAULT AS IDENTITY を設定しています。

psql メタコマンドの \d コマンドを使って作成したテーブルのカラムの情報を取得してみます。

id カラムの「デフォルト」列に generated by default as identity と表示されています。

それでは id カラムには値を指定せずにデータを 3 つ追加してみます。

insert into myschema.branchlist (name) values
  ('Tokyo'), 
  ('Chiba'), 
  ('Kanagawa');

データを追加したあとにテーブルからデータを取得してみると、 id カラムには自動的に 1, 2, 3 と連続した数値が格納されています。

今度は id カラムにも値を指定してデータを追加してみます。

insert into myschema.branchlist values (10, 'Saitama');

GENERATED BY DEFAULT AS IDENTITY が設定された id カラムに値を指定してデータを追加してもエラーとはならず、追加されたデータの id カラムには指定した値が格納されました。

もし GENERATED BY DEFAULT AS IDENTITY が設定されたカラムに値を指定してデータを追加した場合に、指定した値は無視して自動で設定される値を格納したい場合には、 INSERT コマンドを実行する時に OVERRIDING USER VALUE をつけて実行してください。

insert into myschema.branchlist overriding user value
  values (12, 'Osaka');

追加されたデータの id カラムの値は、 INSERT コマンドで指定した値ではなく自動で生成された値が格納されました。

シーケンスオプションを設定する

GENERATED ALWAYS AS IDENTITY を設定した場合も GENERATED BY DEFAULT AS IDENTITY を設定した場合もシーケンスを使って連続した値を生成しており、シーケンスと同じようにシーケンスオプションを設定することができます。

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options )
)

ここではシーケンスオプションを 2 つ試してみます。

-- --

最初に増加量を設定する INCREMENT BY increment オプションです。 increment に指定した値だけ次の値は増減します。正の値だけでなく負の値も設定できます。

では実際に試してみます。mydb データベースの myschema スキーマの中に次のようなテーブルを作成しました。

create table myschema.friends (
  id integer generated always as identity (increment by 5), 
  name varchar(10)
);

テーブルにデータを 3 つ追加します。

insert into myschema.friends (name) values
  ('Suzuki'), 
  ('Endou'), 
  ('Yamada');

データを追加したあとにテーブルからデータを取得してみると、 id カラムには 1, 6, 11 という値が格納されており、 INCREMENT BY オプションで指定した 5 ずつ増加しているのが確認できます。

-- --

次にシーケンス番号の開始番号を設定する START WITH start オプションです。 start に指定した値から自動で設定される値が開始します。

では実際に試してみます。mydb データベースの myschema スキーマの中に次のようなテーブルを作成しました。

create table myschema.friends (
  id integer generated always as identity (start with 50), 
  name varchar(10)
);

テーブルにデータを 3 つ追加します。

insert into myschema.friends (name) values
  ('Suzuki'), 
  ('Endou'), 
  ('Yamada');

データを追加したあとにテーブルからデータを取得してみると、 id カラムには 50, 51, 52 という値が格納されており、 START WITH オプションで指定した 50 から自動で格納される数値が開始されているのが確認できます。

-- --

複数のシーケンスオプションを設定する場合はカンマなどで区切らずに続けて記述してください。例えば INCREMENT BY オプションと START WITH オプションの両方を指定する場合は次のように記述します。

create table myschema.friends (
  id integer generated always as identity (increment by 5 start with 50), 
  name varchar(10)
);

-- --

カラムに GENERATED ALWAYS/BY DEFAULT を設定する方法について解説しました。

著者 / TATSUO IKURA

プログラミングや開発環境構築の解説サイトを運営しています。