はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の19日目のエントリです。
なんというか、だいぶネタ切れ感が出てきましたね。つらい。ネタ自体は勿論多いんだけど、検証したり説明するのが面倒なものも多いのよなあ。
今日も小ネタですいませんです。

今回のお題

PostgreSQLのCREATE TABLE文は指定可能なオプションが非常に多くて、自分も未だに全部覚えていないのだが、今回のお題となったIdentity columnというのは、そのCREATE TABLE文の変更に関わるものである。

ID列制約

PostgreSQL 9.6までのconstraintの指定には、NOT NULL, NULL, CHECK, DEFAULT, UNIQUE, PRIMARY KEY, REFERENCESの種類があったが、PostgreSQL 10では、これに加えてGENERATED

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

という指定が追加された。
たとえば、以下のようにCREATE TABLE文を実行してみる。

ident=# CREATE TABLE parent (id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, data text);
CREATE TABLE

作成したparentテーブルを見ると以下のようになっている。

ident=# \d parent
                           Table "public.parent"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 data   | text    |           |          | 
Indexes:
    "parent_pkey" PRIMARY KEY, btree (id)

id列が、Default列にデフォルト値に自動生成された値が格納されるという設定になっている。

このparentテーブルにデータを格納してみる。

ident=# INSERT INTO parent (data) VALUES ('aaa'),('bbb');
INSERT 0 2
ident=# TABLE parent;
 id | data 
----+------
  1 | aaa
  2 | bbb
(2 rows)

id列に自動生成された値が格納されているのがわかる。

じゃあ、id列に明示的に値を入れてみると・・・

ident=# INSERT INTO parent (id, data) VALUES (3,'ccc');
INSERT 0 1
ident=# INSERT INTO parent (data) VALUES ('ddd');
ERROR:  duplicate key value violates unique constraint "parent_pkey"
DETAIL:  Key (id)=(3) already exists.

入るには入るのだが、後続のINSERTでidが被る場合には、フツーにunique constraintに引っかかる可能性があるので、明示的にid列に値を入れるのは、やっぱり避けたほうが良さげ。

serial型と何が違うの？

実際、Serial型の挙動似ているんだけど、どうやらこの指定方式はSQL標準に準拠しているっぽい。

These are similar to SERIAL columns, but are SQL standard compliant.

CREATE TABLEのlike_optionへの変更

なんかリリースノートにきちんと書かれていないような気がするのだが、identity columnに関して、CREATE TABLE文にもう一つ変更が入っているっぽい。

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

この中にlike_optionというオプションを指定する句があるが、PostgreSQL 10からは、このlike_optionにがIDENTITYキーワードが指定できるようになった。

PostgreSQL 9.6まで

and like_option is:

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }

PostgreSQL 10から

and like_option is:

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL }

では、このIDENTITYという指定は何か。

IDENTITYの効果

like_option(おさらい)

と、その前にlike_optionについて念のために説明。ていうか自分もあんまり使わないオプションなので復習も兼ねて書いてみる。

like_optionというのは、非常にざっくりいうと、source_tableにあるテーブルの全ての列名、そのデータ型、制約を新しいテーブルにコピーする、というもの。よく似た継承(INHERITS)機能と異なるのは、これで作成したテーブルは、元になったテーブルの変更の影響を受けない、ということ。

like_optionをつけたCREATE TABLEの例を以下に示す。

まず、コピー元となるテーブルを作成する。

ident=# CREATE TABLE parent (id int primary key, data text);
CREATE TABLE

次にparentテーブルをsource_tableに指定したchildというテーブルを作成する。

ident=# CREATE TABLE child (LIKE parent, child_data text);
CREATE TABLE

この状態で、両方のテーブル定義を確認してみる。

ident=# \d parent
               Table "public.parent"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 data   | text    |           |          | 
Indexes:
    "parent_pkey" PRIMARY KEY, btree (id)

ident=# \d child
                 Table "public.child"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 id         | integer |           | not null | 
 data       | text    |           |          | 
 child_data | text    |           |          |

コピー元のテーブル(parent)にあった、id, dataの列もchildテーブルにあることがわかる。

ident=# \d parent
               Table "public.parent"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 data   | text    |           |          | 
Indexes:
    "parent_pkey" PRIMARY KEY, btree (id)

ident=# \d child
                 Table "public.child"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 id         | integer |           | not null | 
 data       | text    |           |          | 
 child_data | text    |           |          |

ただ、見てわかるように、childテーブルのid列には、もともとparentテーブルのid列に指定したあった、primary key指定はなく、またそれによって暗黙のうちに作成されるprimary keyインデックスは存在しない。
primary keyインデックスもコピーしたい場合には、like_optionとして、明示的に指定が必要になる。

ident=# CREATE TABLE child (LIKE parent INCLUDING INDEXES, child_data text);
CREATE TABLE
ident=# \d child
                 Table "public.child"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 id         | integer |           | not null | 
 data       | text    |           |          | 
 child_data | text    |           |          | 
Indexes:
    "child_pkey" PRIMARY KEY, btree (id)

IDENTITY指定

前置きが長くなったが、PostgreSQL 10ではlike_optionとして、新たにIDENTITYという指定が可能になった。これは最初のほうに書いた、GENERATED ... AS IDENTITYに指定していた列のシーケンスもコピー先に反映させる、というものだ。

さっきのようにidentity columnをもったparentテーブルを作成して、それをソースとしたchildというテーブルを作成してみる。

まず、parentテーブルを作成。

ident=# CREATE TABLE parent (id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, data text);
CREATE TABLE
ident=# \d parent
                           Table "public.parent"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 data   | text    |           |          | 
Indexes:
    "parent_pkey" PRIMARY KEY, btree (id)

つぎに、「IDENTIY指定なし」のlike_optionでchildテーブルを作成する。

ident=# CREATE TABLE child (LIKE parent , child_data text);
CREATE TABLE
ident=# \d child 
                 Table "public.child"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 id         | integer |           | not null | 
 data       | text    |           |          | 
 child_data | text    |           |          |

これだと、id列は単なるnot null制約のついたinteger型にしかすぎない。
で、childテーブルを一旦削除し、「IDENTIY指定あり」のlike_optionでchildテーブルを作成する。

ident=# CREATE TABLE child (LIKE parent INCLUDING IDENTITY, child_data text);
CREATE TABLE
ident=# \d child
                              Table "public.child"
   Column   |  Type   | Collation | Nullable |             Default              
------------+---------+-----------+----------+----------------------------------
 id         | integer |           | not null | generated by default as identity
 data       | text    |           |          | 
 child_data | text    |           |          |

今度はデフォルト時に自動生成されるという属性も引き継げた。
なお、Primary key指定による、暗黙のインデックス設定を引き継ぎたい場合には、INCLUDING INDEXESも併記しないといけないので注意。

ident=# CREATE TABLE child (LIKE parent INCLUDING IDENTITY INCLUDING INDEXES, child_data text);
CREATE TABLE
ident=# \d child
                              Table "public.child"
   Column   |  Type   | Collation | Nullable |             Default              
------------+---------+-----------+----------+----------------------------------
 id         | integer |           | not null | generated by default as identity
 data       | text    |           |          | 
 child_data | text    |           |          | 
Indexes:
    "child_pkey" PRIMARY KEY, btree (id)

おわりに

PostgreSQLって独自のSQLコマンドや、独自の文法拡張もそれなりに多いんだけど、スタンスとしてはSQL標準にはなるべく従おうというスタンスなんだろうなあ。今回説明した、identtity columnsもその例なんだろう。
like_option修正がリリースノートに明記されていないような気がする件は、docsのMLに投げておいたほうがいいんだろうかなあ。

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.5. Data Types

Add identity columns for assigning a numeric value to columns on insert (Peter Eisentraut)

Identity columns