Pages

2017年5月9日火曜日

PostgreSQLのシーケンス操作

しばらくぶりです。例の新人です。

今回は、PostgreSQLのシーケンス操作について学んだことを記述します。

シーケンスは、PostgreSQLで連番を自動発生させるための仕組みであり、シーケンスオブジェクトともシーケンスジェネレータとも呼ばれます。この仕組みにより、INSERT文でデータを入力する際に、シーケンス設定がされているカラムは自動的に連番の値を取得して登録されます。

例えば、以下のような表があるとします。

TABLE 花
ID   |色   |値段
ーーーーーーーーーーーーーーーーーー
1   |赤   |300
ーーーーーーーーーーーーーーーーーー
2   |白   |250
ーーーーーーーーーーーーーーーーーー

これに対し、以下のようなINSERT文でデータを追加します。

INSERT INTO 花 (色, 値段) VALUES (黄, 350)

すると、表は以下のようになります。

TABLE 花
ID   |色   |値段
ーーーーーーーーーーーーーーーーーー
1   |赤   |300
ーーーーーーーーーーーーーーーーーー
2   |白   |250
ーーーーーーーーーーーーーーーーーー
3   |黄   |350
ーーーーーーーーーーーーーーーーーー

新しく追加した350円の黄色の花のIDに、3が割り当てられました。これは、この表においてIDがシーケンス設定されているためです。プライマリキー制約を設定することで、シーケンスは自動的に作成されます。

シーケンスは、以下のようなSELECT文から情報を確認することができます。

SELECT * FROM [シーケンス名]

シーケンスのカラムは、以下のようになっています。

  sequence_name  | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
-----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 花_id_seq     |           3 |                1 |                   1 | 9223372036854775807 |         1 |           1 |       1 | f         | t


「last_value」の値が現在の値であり、上記の表のIDにおけるシーケンスでは3が入ることなになます。また、「increment_by」は増分値、つまり次の番号に対して現在の値に追加する分の数値であり、上の表の場合は1、2、3と1ずつ追加されているため1ということになります。

以下のようなALTER文を使い、「increment_by」の数値を2にしてみましょう。

ALTER SEQUENCE 花_id_seq INCREMENT BY 2

この後に、以下のINSERT文からデータを追加します。

INSERT INTO 花 (色, 値段) VALUES (青, 300)

すると、表は以下のようになります。

TABLE 花
ID   |色   |値段
ーーーーーーーーーーーーーーーーーー
1   |赤   |300
ーーーーーーーーーーーーーーーーーー
2   |白   |250
ーーーーーーーーーーーーーーーーーー
3   |黄   |350
ーーーーーーーーーーーーーーーーーー
5   |青   |300
ーーーーーーーーーーーーーーーーーー

前回のIDの値3に2増加し、IDが5になりました。それでは、以下のように「increment_by」の数値を1に戻し、INSERT文からデータを追加してみましょう。

ALTER SEQUENCE 花_id_seq INCREMENT BY 1

INSERT INTO 花 (色, 値段) VALUES (ピンク,250)

すると、表は以下のようになります。

TABLE 花
ID   |色   |値段
ーーーーーーーーーーーーーーーーーー
1   |赤   |300
ーーーーーーーーーーーーーーーーーー
2   |白   |250
ーーーーーーーーーーーーーーーーーー
3   |黄   |350
ーーーーーーーーーーーーーーーーーー
5   |青   |300
ーーーーーーーーーーーーーーーーーー
6   |ピンク |250
ーーーーーーーーーーーーーーーーーー

もとどおり、IDの値が前回から1増加した6になりました。

このように、シーケンスの増分値を変更することで、奇数でつけていく、5の倍数でつけていくといった連番のつけ方が可能になります。

次に、現在の値の変更方法を見てみましょう。現在の値を変更するには、SETVAL関数を使います。

SETVAL関数は、シーケンスの現在値を設定するための関数で、以下のような2つのパラメータを持たせたSELECT文形式で使用します。

SELECT SETVAL([シーケンス名],[変更させたい値])

変更させたい値として任意の数値を入れることにより、現在の値と次に割り振られる値を変更させることができます。例えば、以下のようなSELECT文から現在値を7にしてみます。

SELECT SETVAL('花_id_seq',7)

こうすると、次回に割り振られる花のIDは7の次の値である8になります。

しかし、ここで注意しなければいけないことがあります。それは、現在値を1にする方法です。

例えば、上のように以下のやり方で現在値を1にした場合、次に割り振られる花のIDは2になります。

SELECT SETVAL('花_id_seq',1)

これは、シーケンスのカラムの1つである「is_called」が「true」になっているためです。

「is_called」は「last_value」が既に呼ばれたかどうかのフラグで、呼び出されている場合は真(true)、まだ呼び出されていない場合は偽(false)になります。

シーケンスが連番を発生させるためには現在値を知る必要がありますので、連番を作成する時点でシーケンスは「last_value」を呼び出します。際初期段階においては、ここで「is_called」が真になります。「is_called」が偽の場合とは、シーケンスがまだ連番を発生させていない状態、すなわちまだ連番になるデータがない状態を意味します。

つまり、同じ現在値(「last_value」)が1の場合でも、「is_called」の真偽で最初期状態か1の値が割り振られたデータがある状態かに分かれます。

シーケンスの連番を新たに1から始め直すには、以下のSETVAL関数に3つのパラメータを持たせたSELECT文を使用します。

SELECT SETVAL('花_id_seq',1,false)

こうすることで、「is_called」が「false」になり、次回に割り振られる花のIDは1になります。ちなみに、falseの代わりにtrueを持たせた場合は、パラメータが2つの場合と同じ結果になります。