home ホーム search 検索 -  login ログイン  | reload edit datainfo version cmd icon diff delete  | help ヘルプ

技術/PostgreSQL/後付け"serial"設定方法メモ (v1)

技術/PostgreSQL/後付け"serial"設定方法メモ (v1)

技術 / PostgreSQL / 後付け"serial"設定方法メモ (v1)
id: 725 所有者: msakamoto-sf    作成日: 2010-08-02 08:20:39
カテゴリ: PostgreSQL 

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 です。

とりあえずテーブルを作ってみます。

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

正解は、1.sequenceを作成し、2.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()をカラムに設定すると良いでしょう。



プレーンテキスト形式でダウンロード
現在のバージョン : 1
更新者: msakamoto-sf
更新日: 2010-08-02 08:37:39
md5:e1b8d4c2353ee2388dd3a97c99fbf62b
sha1:53e2daf11b712153f5f6fb2210f4176b3dfcab5e
コメント
コメントを投稿するにはログインして下さい。