#navi_header|技術|

PostgreSQLの少なくとも7.4以降では、CREATE TABLE実行時に "serial" を型に指定すると、対応するシーケンスを自動生成してnextval()してくれるようになります。

今回は、連番キー値が格納されるが主キー設定もされていないしシーケンスも使われていないカラムに対し"serial"型のような自動採番を設定する方法についてメモしておきます。
つまり、
 CREATE TABLE foo (id integer, name text);
これを、
 CREATE TABLE foo (id serial PRIMARY KEY, name text);
したことにします。

なお動作確認は PostgreSQL 8.1 です。

#more||
とりあえずテーブルを作ってみます。
 CREATE TABLE foo (id integer, name text);
 INSERT INTO foo (id, name) VALUES (1, 'abc');
 INSERT INTO foo (id, name) VALUES (3, 'def');

この"id"カラムに対して自動採番を設定していきます。

まず主キー制約を作成します。
 ALTER TABLE ONLY foo ADD CONSTRAINT foo_pkey PRIMARY KEY (id);

続いてidの型をserialに・・・するのは間違いです。ALTER TABLE からでは "serial" を指定出来ません。
 ALTER TABLE foo ALTER COLUMN id TYPE serial;
 →
 ERROR:  type "serial" does not exist

正解は、１．sequenceを作成し、２．DEFAULT値にnextval()を設定します。
sequence名は「テーブル名 + "_" + カラム名 + "_seq"」にしておくと無難です。
 CREATE SEQUENCE foo_id_seq;
 ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq');

最後に、現在のsequence値を設定します。
 SELECT setval('foo_id_seq', 3);
 or
 SELECT setval('foo_id_seq', 4, false);

以上で自動採番が使えるようになります。

ただし、これは
 id serial PRIMARY KEY
と完全に同義ではありません。
serialの場合はpg_dumpが出力するCREATE TABLEにおいても
 id serial
となり、CREATE SEQUENCEはpg_dumpの出力には含まれません。
シーケンス値の設定も
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('テーブル名', 'カラム名'), xxxx, true);
として設定されます。

つまり"serial"にすると、シーケンスの生成や破棄がテーブルと連動するよう裏側で調整されます。

一方、上記の手順に従い手動で設定した場合は
 id ... DEFAULT nextval(...) ...
となり、CREATE SEQUENCE文＋setval() がpg_dumpの出力に含まれます。シーケンスの生成・破棄はテーブルと連動せず、独立して管理されます。

PostgreSQL 8.2 以降であれば、
ALTER SEQUENCE シーケンス名 OWNED BY テーブル名 + "." + カラム名
を使うことで、serialを指定した時と同様、テーブルと連動するよう後から変更出来ます。

ざっと調べてみたところ、8.1以前は ALTER SEQUENCE ... OWNED BY が使えないようです。
また、これに同等なSQLなども見当たらなかったため、8.1以前ではserialと同様にテーブルと連動するシーケンスを後付けで設定することは出来ないと思われます。

まとめると、
- "serial"を指定したいならCREATE TABLEの段階で指定する。
- 後から "serial" と同様の自動採番を設定することも出来る。
- 8.1以前の場合は、"serial" と同様にテーブルと連動したシーケンスの自動管理を、後から設定することは無理そう。

便利な "serial" ですが、適用できないケースもあります。
"serial"指定によりテーブルと連動するシーケンスは、1:1の関係で裏側で連動する為、他のテーブルから自由に使うのが難しくなります。
例えばテーブルとDROPすると、連動していたシーケンスも自動的にDROPされます。他のテーブルからそのシーケンスを使っていると困ったことになります。
よって、一つのシーケンスを複数のテーブルの自動採番に使いたい場合は、"serial"は使わずに、今回のメモのように手動でnextval()をカラムに設定すると良いでしょう。

#navi_footer|技術|