シーケンスという連番自動生成機能とシーケンスによるシリアル型について.
シーケンスの作成と削除
シーケンス操作関数
シリアル型
シーケンスの作成と削除
シーケンスを作成するには,CREATE SEQUENCEを実行する(※).
firstdb=> \h CREATE SEQUENCE
Command: CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache ] [ CYCLE ]
各オプションについて以下に示す.
TEMPORARY,TEMP
セッション内でのみ有効な一時シーケンスを作成.
seqname
シーケンス名.DB内で一意でなければならない.
INCREMENT increment
増加幅をincrementとする.デフォルトは1.負の値を指定すると減少するシーケンスを作成できる.
MINVALUE minvalue
シーケンスの最小値をminvalueとする.デフォルトは増加シーケンスでは1,減少シーケンスでは-(231). MAXVALUE maxvalue シーケンスの最大値をmaxvalueとする.デフォルトは増加シーケンスでは231,減少シーケンスでは-1.
START start
シーケンスの初期値をstartとする.デフォルトは増加シーケンスではminvalue,減少シーケンスではmaxvalue.
CACHE cache
高速化のために各バックエンドプロセスがcache個のシーケンスを一度に確保する.確保する値はバックエンド間で重複することはない.複数のバックエンドの実行順は保証されないので,使用されるシーケンス値の順番が前後することがある.キャッシュした値が最終的に使用されなかった場合は,その値は失われる.例えばバックエンドB1,B2がそれぞれシーケンス{1,2,3},{4,5,6}をキャッシュしているとする.ここで,B1,B2,B2,B1,B1の順番でバックエンドが動いた場合,消費されるシーケンス値は順に1,4,5,2,3であり,順番が前後する.ここでB2にはシーケンス値6が消費されずに残っているが,このままB2が終了すると,値6は使用されずに消失する.
CYCLE
サイクリックなシーケンスにする時に使用する.サイクリックな増加シーケンスではmaxvalueに達したらminvalueにもどる.サイクリックな減少シーケンスではその逆.
シーケンスを削除するにはDROP SEQUENCEを実行する.
DROP SEQUENCE seqname;
※ シーケンスはSQL標準ではない.
シーケンス操作関数
作成したシーケンスは,それ用の関数によって操作する.
nextval('シーケンス名')
次のシーケンス値を返す.
currval('シーケンス名')
現在のシーケンス値を返す.同じセッション内でnextval実行後でないとエラーになる.現在のシーケンス値はcurrvalのかわりに以下でも取り出せる.
SELECT last_value FROM シーケンス名;
setval('シーケンス名', 値)
シーケンスの値を設定する.
では,実際にやってみる.
firstdb=> CREATE SEQUENCE test_seq;
CREATE
firstdb=> \d test_seq
Sequence "test_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
作成したシーケンスtest_seqを使用するテーブルhogeを作成.
firstdb=> CREATE TABLE hoge (
firstdb(> seq INTEGER DEFAULT nextval('test_seq'),
firstdb(> val INTEGER);
CREATE
hogeにINSERTしてみる.
firstdb=> INSERT INTO hoge(val)
firstdb-> VALUES (1);
INSERT 26156 1
firstdb=> INSERT INTO hoge(val)
firstdb-> VALUES (1);
INSERT 26157 1
firstdb=> INSERT INTO hoge(val)
firstdb-> VALUES (1);
INSERT 26158 1
firstdb=> SELECT * FROM hoge;
seq | val
-----+-----
1 | 1
2 | 1
3 | 1
(3 rows)
シーケンスtest_seqを消してみる.
firstdb=> DROP SEQUENCE test_seq ;
DROP
firstdb=> INSERT INTO hoge(val)
firstdb-> VALUES (1);
ERROR: pg_aclcheck: class "test_seq" not found
確かにシーケンスが消えているようだ.なお,シーケンスはROLLBACKしても元には戻らない.シリアル型
シリアル型は1から始まる増加シーケンスを使用したデータ型である.シリアル型を使用すると,自動的にシーケンスが作成され,その属性でインデクスが作成される.テーブルをDROPしてもシーケンスはDROPされないので,以下のパターンで作成されたシーケンスを別途DROPする必要がある.
テーブル名_属性名_seq
では,やってみる.
firstdb=> CREATE TABLE hoge (
firstdb(> id SERIAL,
firstdb(> val INTEGER);
NOTICE: CREATE TABLE will create implicit sequence ‘hoge_id_seq’ for SERIAL column ‘hoge.id’
NOTICE: CREATE TABLE / UNIQUE will create implicit index ‘hoge_id_key’ for table ‘hoge’
CREATE
あとは,シーケンスの場合と同様に扱える.
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26164 1
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26165 1
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26166 1
firstdb=> SELECT * FROM hoge ;
id | val
—-+—–
1 | 1
2 | 1
3 | 1
(3 rows)
SERIALはUNIQでなければならない.
firstdb=> INSERT INTO hoge (id,val)
firstdb-> VALUES (1,2);
ERROR: Cannot insert a duplicate key into unique index hoge_id_key
さらにSERIAL型の列に自分で勝手に値を放り込んでも,シーケンスにはそれがわからないので,できるだけ避けたい.
firstdb=> INSERT INTO hoge (id,val)
firstdb-> VALUES (5,2);
INSERT 26168 1
firstdb=> SELECT * FROM hoge ;
id | val
—-+—–
1 | 1
2 | 1
3 | 1
5 | 2
(4 rows)
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26169 1
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
ERROR: Cannot insert a duplicate key into unique index hoge_id_key
firstdb=> INSERT INTO hoge (val)
firstdb-> VALUES (1);
INSERT 26171 1
firstdb=> SELECT * FROM hoge ;
id | val
—-+—–
1 | 1
2 | 1
3 | 1
5 | 2
4 | 1
6 | 1
(6 rows)
最後に消しておく.
firstdb=> DROP TABLE hoge ;
DROP
firstdb=> DROP SEQUENCE hoge_id_seq;
DROP
