PosgreSQL　シーケンスでの重複エラーの解決。

はじめに

先日、開発中にテストデータをデータベースにインサート。
その時に、「プライマリーキーの重複エラー」に遭遇しました。

今回は、このエラーの「プライマリーキーの重複エラー」原因と対処を書きます。

原因：Idを手動で追加してしまったこと。

ざっくり原因ですが、「シーケンスの追加を手動でしてしまっていた」ことです。

まずは、下記のSQLを実行して、シーケンスがどうなるか確認してみます。

-- テーブル追加
create table people (id serial primary key, name text);

-- データ挿入
INSERT INTO test (name) VALUES ('hoge');
INSERT INTO test (name) VALUES ('fuga');

SELECT * FROM test;
 id | name 
----+------
  1 | hoge　　<- id は 1
  2 | fuga　　<- id は 1にプラス１されて、２

見て分かるとおり、
idは、インサートするごとに、シーケンスの最大値 + 1の値が自動で挿入されていきます。

では、次の場合はどうでしょうか。

-- データ挿入
INSERT INTO test(id, name) VALUES (3, 'hogefuga');

SELECT * FROM test;
 id |   name   
----+----------
  1 | hoge
  2 | fuga
  3 | hogefuga <- 無事に追加されている。

上記のに、普通にインサートできているように見えます。
しかし、次のようにインサートしようとすると、下記のようなエラーが出てしまいます。

-- データ挿入
INSERT INTO test (name) VALUES ('fugefuga');

-- エラー発生！！
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(3) already exists.

エラーの内容は、「プライマリーキーの重複エラー。id = 3はすでに登録されているよ」とのことです。

え、id = 3は、さっき登録したじゃん。

シーケンスのインクリメントタイミング

もうお分かりかもしれませんが、
シーケンスのインクリメントのタイミングを理解しておくことが重要です。

初めのSQLにあるように、
idを指定しないでインサートしたときに、シーケンスの値がインクリメントされます。

一方最後のSQLにあるように、
idを指定して、
インサートするとシーケンスの値はインクリメントされずに上記のエラーが発生します。

対処：シーケンスのズレを修正

シーケンスのズレを修正するには、下の手順をするだけ。
①シーケンスの最大値を取得
②シーケンスに最大値をセットする

なので、下のSQLで対処OK。

-- シーケンスの最大値を取得
SELECT MAX(id) FROM test;
 max 
-----
   3

-- シーケンスに最大値＋１をセット
SELECT setval('test_id_seq', (SELECT max(id) FROM test));
 setval 
--------
      3

-- 挿入
INSERT INTO test (name) VALUES ('fugefuga');

SELECT * FROM test;
 id |   name   
----+----------
  1 | hoge
  2 | fuga
  3 | hogefuga
  4 | fugefuga