PostgresのSerial型のPrimary keyの挙動について

TL;DR

ERROR: duplicate key value violates unique constraintがpostgresで起こる原因の1つとして、Serial型の採番が行われないことによってPrimary keyの整合が取れない場合が挙げられれます。 この問題が起こるのは基本的には手動でPrimary keyに値を代入した時に起こりやすいです。

この問題の解決策は多くの記事で述べられていますが、挙動そのものについてはあまり触れられていなかったため、本記事ではその時シーケンスオブジェクトがどうなっているのかの挙動の確認を置いておきます。

環境

以下のdocker-compose.yamlを利用します。

services:
  postgres:
    image: postgres:16
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres

docker compose exec postgres /bin/bash
psql -U postgres

シーケンスオブジェクト (Sequence Object) に関する検証

テーブル準備とスキーマの説明

検証用のテーブルを作ります。明示的にprimary keyにはSerial型を与えます。

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

テーブルができていることを確認します。usersテーブルとSerial型のusers_id_seqができています。

postgres=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 public | users        | table    | postgres
 public | users_id_seq | sequence | postgres
(2 rows)

usersテーブルのスキーマを確認します。Default値はnextval('users_id_seq'::regclass)になっています。

postgres=# \d users
                                    Table "public.users"
 Column |          Type          | Collation | Nullable |              Default              
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(255) |           | not null | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

nextvalはSequence Manipulation functionsです。公式の説明は以下です。

Advances the sequence object to its next value and returns that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value. If the sequence object has been created with default parameters, successive nextval calls will return successive values beginning with 1. Other behaviors can be obtained by using appropriate parameters in the CREATE SEQUENCE command.

nextval自体はシーケンスオブジェクトを採番して次の値に進め、その値を返す関数です。これによって、INSERT時のauto incrementが行われます。 また、言い換えると、このDefaultが呼ばれない限り採番が行われません。

実際に挙動を確認する

まず、初期状態に対してINSETして採番がどうなるかを確認します。currval関数を使って、現状のシーケンスオブジェクトを取得しています。 currval関数は、シーケンスオブジェクトの現在の数を返します。

これを見るとちゃんと採番されていることがわかります (シーケンスオブジェクトは1-indexです)。この場合は問題は起こりません。

INSERT INTO users (name) VALUES
('John'),
('Mary');

SELECT currval('users_id_seq');
-- 2

一回DROP TABLE users;をしてテーブルを再作成します。その後IDを明示してみると、現状のシーケンスオブジェクトを取得しようとすると採番がされておらずエラーすることがわかります。 また、この後idを明示せずにINSERTを試みると、冒頭で記述したduplicate key value violates unique constraintが起こります。

ちなみにINSERTが失敗しても採番されます。なので後2回同じことをすれば最後には成功します。これはDefaultのnextval('users_id_seq'::regclass)が毎回呼ばれるため起こります。

INSERT INTO users (id, name) VALUES
(1, 'John'),
(2, 'Mary');

SELECT currval('users_id_seq');
-- ERROR:  currval of sequence "users_id_seq" is not yet defined in this session

INSERT INTO users (name) VALUES
('Bob');
-- ERROR:  duplicate key value violates unique constraint "users_pkey"
-- DETAIL:  Key (id)=(1) already exists.

SELECT currval('users_id_seq');
-- 1

解決方法

一旦またDROP TABLE usersをしてテーブルを再作成します。

要はcurrvalで取得できる値の辻褄があっていればいいわけです。 なので、setvalを使って現状のテーブルのprimary keyの最大値を対応するシーケンスオブジェクトに書き込めば解決します。

INSERT INTO users (id, name) VALUES
(1, 'John'),
(2, 'Mary');

SELECT currval('users_id_seq');
-- ERROR:  currval of sequence "users_id_seq" is not yet defined in this session

SELECT setval(
    pg_get_serial_sequence('users', 'id'), 
    (SELECT MAX(id) FROM "users")
);
-- 2

SELECT currval('users_id_seq');
-- 2

INSERT INTO users (name) VALUES
('Bob');
-- INSERT 0 1

SELECT currval('users_id_seq');
-- 3

SELECT setval(
    pg_get_serial_sequence('users', 'id'), 
    (SELECT MAX(id)+1 FROM "users"),
    false
);

SELECT setval('myseq', 42);           Next nextval will return 43
SELECT setval('myseq', 42, true);     Same as above
SELECT setval('myseq', 42, false);    Next nextval will return 42