PostgreSQLのSERIAL型

前置き

postgresが提供する、SERIALという型に関する話しです。他のDBMSではAUTO_INCREMENTプロパティと呼ばれているらしいです。テーブルの主キー列に、自動的に一意な数値をふりたいときなどに使います。

本文

基本

まずは、使用例を示します。

mydb=# CREATE TABLE pref_t (id SERIAL, name TEXT);
NOTICE:  CREATE TABLE will create implicit sequence "pref_t_id_seq"
for serial column "pref_t.id"
CREATE TABLE

このように、SERIAL型を使ったテーブルを作成すると、一緒にシーケンスジェネレータ(シーケンスと略す)と呼ばれるものが自動生成されます。シーケンスは1行だけのテーブルみたいなもので、\dコマンドで見ると、sequence_nameやlast_valueといった列を持っているのが分かります。

mydb=# \d pref_t_id_seq
Sequence "public.pref_t_id_seq"
    Column     |  Type
---------------+---------
 sequence_name | name
 last_value    | bigint
 increment_by  | bigint
 max_value     | bigint
 min_value     | bigint
 cache_value   | bigint
 log_cnt       | bigint
 is_cycled     | boolean
 is_called     | boolean

上の例では、pref_tテーブルのid列にSERIAL型を使っていますが、SERIAL型は厳密な型ではなくシンタックスシュガーのようです。\dコマンドで見ると、id列の本当の型はINTEGER型であり、デフォルト値としてnextval('pref_t_id_seq'::regclass)が使われていることが分かります。

mydb=# \d pref_t
                         Table "public.pref_t"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id     | integer | not null default nextval('pref_t_id_seq'::regclass)
 name   | text    |

このnextval()がシーケンスから新しい連番を取り出す関数になります。いくつか行を追加してみましょう。

mydb=# INSERT INTO pref_t (name) VALUES ('Hiroshima');
INSERT 0 1

mydb=# INSERT INTO pref_t (name) VALUES ('Fukuoka');
INSERT 0 1

mydb=# SELECT * FROM pref_t;
 id |   name
----+-----------
  1 | Hiroshima
  2 | Fukuoka
(2 rows)

mydb=# SELECT last_value FROM pref_t_id_seq;
 last_value
------------
          2
(1 row)

nextval()は、単独で呼んでも構いません。

mydb=# SELECT nextval('pref_t_id_seq');
 nextval
---------
       3
(1 row)

mydb=# SELECT nextval('pref_t_id_seq');
 nextval
---------
       4
(1 row)

mydb=# SELECT last_value as newid FROM pref_t_id_seq;
 newid
-------
     4
(1 row)

最後に取得した連番は?

CGIなどを使ってDBに行を追加したあとで、たった今追加した行の連番が何だったか知りたい場合があります。その方法として候補を3つ挙げてみます。

mydb=# SELECT max(id) as newid FROM pref_t;
 newid
-------
     2
(1 row)

mydb=# SELECT last_value as newid FROM pref_t_id_seq;
 newid
-------
     2
(1 row)

mydb=# SELECT currval('pref_t_id_seq') as newid;
 newid
-------
     2
(1 row)

正解は最後の方法です。最初の2つは、同じDBに対して複数のセッションが存在していた場合に、排他制御上の問題がありますね。それに対してcurrval()は、当該セッション内で、指定されたシーケンスに対する最後のnextval()が返した連番を返してくれます。

また、引数なしのlastval()という関数もあります。こちらはシーケンス名は問わず、当該セッション内での最後のnextval()が返した値を返します。

currval()もlastval()も、当該セッション内で一度もnextval()が呼ばれてない場合はエラーになります。

mydb=# SELECT lastval();
ERROR:  lastval is not yet defined in this session

テーブルとシーケンスの依存性

テーブル生成後も、シーケンスとの依存性はpostgresによって管理されています。例えばシーケンスの名前を変えると、テーブルの列定義の方も変わります。

mydb=# ALTER TABLE pref_t_id_seq RENAME TO pref_t_id_sequence;
ALTER TABLE

mydb=# \d pref_t
                            Table "public.pref_t"
 Column |  Type   |                        Modifiers
--------+---------+----------------------------------------------------------
 id     | integer | not null default nextval('pref_t_id_sequence'::regclass)
 name   | text    |

しかし不思議なことに、シーケンスの名前を変えても、sequence_name列の値は変わりません。

mydb=# SELECT sequence_name FROM pref_t_id_sequence;
 sequence_name
---------------
 pref_t_id_seq
(1 row)

SERIAL型が使われている列をDROPしたり、テーブル自体をDROPすると、シーケンスもDROPされます。シーケンスだけをDROPすることはできません。

mydb=# ALTER TABLE pref_t DROP COLUMN id;
ALTER TABLE

mydb=# \d pref_t_id_sequence
Did not find any relation named "pref_t_id_sequence".

もう一つ不思議なことに、SERIAL型の列に使うシーケンスを別のシーケンスに取り替えた後でも、使わなくなった方のシーケンスだけをDROPすることはできません。

mydb=# ALTER TABLE pref_t ADD COLUMN id serial;
NOTICE:  ALTER TABLE will create implicit sequence "pref_t_id_seq"
for serial column "pref_t.id"
ALTER TABLE

mydb=# CREATE SEQUENCE pref_t_id_new_seq;
CREATE SEQUENCE

mydb=# ALTER TABLE pref_t ALTER COLUMN id SET DEFAULT
mydb-# nextval('pref_t_id_new_seq');
ALTER TABLE

mydb=# \d pref_t;
                           Table "public.pref_t"
 Column |  Type   |                        Modifiers
--------+---------+---------------------------------------------------------
 name   | text    |
 id     | integer | not null default nextval('pref_t_id_new_seq'::regclass)

mydb=# DROP SEQUENCE pref_t_id_seq;
ERROR:  cannot drop sequence pref_t_id_seq because table pref_t
column id requires it
HINT:  You may drop table pref_t column id instead.

テーブルの列とシーケンスとの依存関係に関して、postgresが最新状態に追従できてないようです。postgresのシステムカタログ情報を参照してみると…

mydb=# SELECT pg_catalog.pg_get_serial_sequence('pref_t', 'id');
pg_get_serial_sequence
------------------------------------
public.pref_t_id_seq

なので、この後でテーブルをDROPすると、使われなくなった方のシーケンスがDROPされます。

mydb=# DROP TABLE pref_t;
DROP TABLE

mydb=# \d
                    List of relations
 Schema |            Name             |   Type   | Owner
--------+-----------------------------+----------+-------
 public | pref_t_id_new_seq           | sequence | xxxx
(1 row)

なお、テーブル間の依存関係を管理しているシステムカタログは、pg_dependです。これを更新すれば、壊れた依存関係を修復できそうですが、怖いのでやめておきましょう。

mydb=# SELECT oid FROM pg_class WHERE relname ='pref_t_id_seq';
  oid
-------
 16497
(1 row)
mydb=# SELECT oid FROM pg_class WHERE relname ='pref_t';
  oid
-------
 16499
(1 row)
mydb=# SELECT * FROM pg_depend WHERE objid = 16497 AND refobjid = 16499;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
    1259 | 16497 |        0 |       1259 |    16499 |           1 | i
(1 row)

最後にもう一つ。上記の例に、ときどき ::regclass という表記がありますが、これはキャストです。regclassはオブジェクトID(oid)の一種で、リレーション(テーブルやビュー)用のoidです。つまり、リテラルをリレーションIDに変換しているわけですね。なので、最後のSQL文は以下のようにも書けます。

mydb=# SELECT * FROM pg_depend WHERE objid = 'pref_t_id_seq'::regclass
mydb-# AND refobjid = 'pref_t'::regclass;
Last modified:2010/05/28 10:45:12
Keyword(s):
References:[サーバ管理・Linux関連]
This page is frozen.