Zenn
Open3

Postgresqlのserial型カラムのシーケンスを取得・設定したい

アルパカ大明神アルパカ大明神

現象

Postgresqlのローカル環境構築で、serial型に対して値を指定して初期データロードした結果、シーケンス値が進まずに登録されてしまってInsert時にDuplicate Errorになってしまうという事案があった。

-- create
> CREATE TABLE animal (
      id SERIAL,
      name VARCHAR(10),
      PRIMARY KEY(id)
   );
-- initial load
> INSERT INTO animal (id, name) VALUES (1, 'cat');
> INSERT INTO animal (id, name) VALUES (2, 'dog');

-- insert (without id)
> INSERT INTO animal (name) VALUES ('tiger');
ERROR:  duplicate key value violates unique constraint "animal_pkey"
DETAIL:  Key (id)=(1) already exists.

この現象に対しては、当たり前だがinitial load時にはserial型に値を指定せず登録すれば問題は発生しなくなる。
が、そもそも現在のserial型のシーケンス値を確認・更新するにはどうしたら良いんだろう？ということで調べてみた。


serial型のシーケンス名取得: pg_get_serial_sequence

兎にも角にも、まずはシーケンス名を取得する必要がある。
そして結論から言うと pg_get_serial_sequence 関数を使えば良い。
フォーマットとしては以下のような感じ。

-- format
pg_get_serial_sequence([table_name], [column_name])

先の例だと、以下でシーケンス名を取得できる。

> SELECT pg_get_serial_sequence('animal', 'id');
 pg_get_serial_sequence 
------------------------
 public.animal_id_seq

serial型のシーケンス値取得: currval

シーケンス名が分かれば後は通常のシーケンス操作をすれば良い。
取得に関しては currval 関数を利用する。

-- format
currval([sequence_name])

先の例だと以下のような感じ。

> SELECT currval('public.animal_id_seq');
 currval 
---------
     1

serial型のシーケンス値更新: setval

更新も同様に、シーケンスを更新する関数 setval を利用すれば良い。

-- format
setval([sequence_name], [value])

先の例だと以下のような感じ。

> SELECT setval('public.animal_id_seq', 10000);
 setval 
---------
 10000

なお、その名の通り次のシーケンスに値をすすめる nextval 関数も存在する。


今回のケースを修正したい場合

上記をワンライナーで書けばいけそう。
たとえば、idの最大値にcurrvalを合わせたければ以下のようなSQLで良さそう。
idのMAXが 2 なので、2が設定されるはず。

> SELECT setval(pg_get_serial_sequence('animal', 'id'), (SELECT MAX(id) FROM animal));
 setval 
--------
      2

当然だが、この後に先程のINSERT文を実行すると問題なく通る。

> INSERT INTO animal (name) VALUES ('tiger');
INSERT 0 1

> SELECT * FROM animal;
 id | name  
----+-------
  1 | cat
  2 | dog
  3 | tiger

Ref

kazuhito_mkazuhito_m

「serial型のシーケンス値更新: setval」の書式説明の箇所、setval と思しきところが serial に成ってると思うのですが、私の勘違いでしょうか。

アルパカ大明神アルパカ大明神

あああたしかに記述間違いでしたので修正いたしました。
ご指摘ありがとうございました！