PostgreSQLで遅延制約を使って一意制約カラムを一括更新する

遅延制約

PostgreSQLで一意制約が設定されたカラムを一括UPDATEした際に、一意制約違反になったことありませんか？
今回、遅延制約という機能を使い、更新を実現する方法を紹介します。

例えば以下のようなテーブルとテストデータを用意し、一括更新してみましょう。

-- TABLE DEFINITION
CREATE TABLE posts(
    id integer NOT NULL,
    CONSTRAINT posts_pk PRIMARY KEY (id)
);

-- TEST DATA
INSERT INTO posts VALUES(1);
INSERT INTO posts VALUES(2);
INSERT INTO posts VALUES(3);

-- UPDATE
UPDATE posts SET id = id + 1;

すると、以下のようなエラーが発生します。

ERROR:  duplicate key value violates unique constraint "posts_pk"
DETAIL:  Key (id)=(2) already exists.

これは、PostgreSQLによってUPDATEが先頭から1件ずつ処理される為、id=1->2に更新される際に一意制約違反となる為です。
これを解消して更新を可能とするのが遅延制約(DEFERRABLE)です。

遅延制約の設定方法

遅延制約は、CREATE TABLE（またはSET CONSTRAINT）の際に設定する必要があります。
以下のようなテーブルを準備します。

-- TABLE DEFINITION
CREATE TABLE posts2(
    id integer NOT NULL,
    CONSTRAINT posts2_pk PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED
);

-- TEST DATA
INSERT INTO posts2 VALUES(1);
INSERT INTO posts2 VALUES(2);
INSERT INTO posts2 VALUES(3);

-- UPDATE
UPDATE posts2 SET id = id + 1;

上記のクエリが正常に実行され、全件が正常にUPDATEされました。
CONSTRAINTとして設定されたDEFERRABLEによって、全件更新完了するまで一意制約が遅延された為です。
今回はINITIALLY DEFERRED句を使い、デフォルトで遅延制約を有効にしていますが、これを省略（またはINITIALLY IMMEDIATE句を指定）した場合にはデフォルトで遅延制約が無効になります。

ここで指定可能な値は以下の通りです。

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

その他の使い方

ユニーク制約で使う

遅延制約はユニーク制約でも使用可能です。

-- TABLE DEFINITION
CREATE TABLE posts3(
    id integer NOT NULL
);
-- ADD CONSTRAINT
ALTER TABLE posts3 ADD CONSTRAINT posts3_uniq_idx UNIQUE (id) DEFERRABLE INITIALLY DEFERRED;

また、遅延制約をデフォルトで無効（INITIALLY IMMEDIATE）にした際、クライアントセッション内で有効にする場合には以下の設定クエリを使用します。

SET CONSTRAINTS ALL DEFERRED;

詳細はPostgreSQLマニュアルをご参照ください。

• CREATE TABLE
• ALTER TABLE
• SET CONSTRAINTS

最後に

遅延制約を設定する際は、必ず制約の作成時に設定する必要があります。
また対象テーブルではON CONFLICT句が使えなくなりますので、ご注意ください。
更新する可能性があるカラムにはお忘れなく！