PostgreSQL で UPSERT ってどうやるの？

PostgreSQL では、他のデータベースシステムで一般的に使用される UPSERT ステートメントは存在しません。しかし、PostgreSQL には UPSERT と同等の機能を実現するための方法が用意されています。本記事では、PostgreSQL で UPSERT を実現する主な方法について解説します。

1. ON CONFLICT 句を使用した INSERT 文

PostgreSQL では、INSERT 文に ON CONFLICT 句を追加することで、UPSERT 操作を実現できます。この方法は、特定の一意制約や排他制約に基づいて挿入操作を制御する際に有効です。

使用例

以下に、ON CONFLICT 句を使用した基本的な INSERT 文の例を示します。

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[e-mail]')
ON CONFLICT (id) 
DO UPDATE SET 
    name = EXCLUDED.name,
    email = EXCLUDED.email;

この例では、users テーブルに新しいレコードを挿入しようとしていますが、id 列に一意制約が設定されている場合、既存の id が存在する場合には DO UPDATE によって既存のレコードが更新されます。

競合時の処理

ON CONFLICT 句では、競合が発生した際の動作を指定できます。指定できる動作は以下 2 つです。

DO UPDATE

競合が発生した場合に既存のレコードを更新します。更新するカラムや値は柔軟に指定できます。

例:

INSERT INTO products (id, name, quantity)
VALUES (1001, 'Widget', 10)
ON CONFLICT (id)
DO UPDATE SET
    quantity = products.quantity + EXCLUDED.quantity;

この例では、既存の id が存在する場合、quantity を既存の値に新たに挿入しようとしている値を加算します。

DO NOTHING

競合が発生した場合に何もせず、エラーを発生させません。挿入をスキップします。

例:

INSERT INTO orders (order_id, customer_id, amount)
VALUES (5001, 300, 250.00)
ON CONFLICT (corder_id) DO NOTHING;

この場合、既存の order_id が存在する場合、新しいレコードは挿入されず、エラーも発生しません。

競合条件を省略できる

ON CONFLICT 句では、DO UPDATE を使用する場合には競合条件（特定の制約やインデックス）を明示的に指定する必要があります。しかし、DO NOTHING を使用する場合には競合条件を省略することができます。この場合、テーブルに設定されているすべての一意制約や排他制約が競合条件として適用されます。

例:

-- DO UPDATE を使用する場合は競合条件を指定する必要がある
INSERT INTO employees (employee_id, name, department)
VALUES (101, 'Bob', 'Sales')
ON CONFLICT (employee_id)
DO UPDATE SET department = EXCLUDED.department;

-- DO NOTHING を使用する場合は競合条件を省略できる
INSERT INTO employees (employee_id, name, department)
VALUES (102, 'Charlie', 'Marketing')
ON CONFLICT DO NOTHING;

更新は行わない

DO NOTHING オプションを使用すると、競合が発生した場合に更新を行わず、単に挿入をスキップします。これは、重複を許容しないが更新を必要としない場合に有用です。

複数の競合条件を指定できない

ON CONFLICT 句では、一度に指定できる競合条件は一つだけです。複数の一意制約やインデックスに基づく競合を同時に処理することはできません。

例:

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3)
ON CONFLICT (col1) DO UPDATE SET ...
ON CONFLICT (col2) DO UPDATE SET ...; -- これはエラーとなる

上記のように、複数の ON CONFLICT 句を同時に使用しようとすると、構文エラーとなります。

トランザクションを使ってもユニーク制約を回避できない

複数の競合条件を処理するためにトランザクションを使用しようとしても、最初の INSERT 操作で一意制約に基づく競合が発生すると、トランザクション全体がエラーとなります。
このため、複数の競合条件を同時に処理する場合には、他の方法を検討する必要があります。

-- テーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT UNIQUE,
    email TEXT UNIQUE
    phone_number TEXT UNIQUE
);

-- トランザクション
BEGIN;

INSERT INTO users (username, email, phone_number)
VALUES ('foo', '[e-mail]', '012345678');

INSERT INTO users (username, email)
VALUES ('hogehoge', '[e-mail]', '012345678')
ON CONFLICT (email)
DO UPDATE; -- phone_number のユニーク制約により、エラーが起きる

INSERT INTO users (username, email)
VALUES ('hogehoge', '[e-mail]', '012345678')
ON CONFLICT (phone_number) 
DO UPDATE;

COMMIT;

2. MERGE ステートメント

PostgreSQL では、SQL 標準の MERGE ステートメントがバージョン 15 以降でサポートされています。MERGE を使用すると、条件に基づいて挿入、更新、削除を一括して行うことができます。これにより、複数の競合条件を柔軟に処理することが可能です。

使用例

以下に、MERGE ステートメントを使用した UPSERT 操作の例を示します。

MERGE INTO target_table AS t
USING source_table AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name,
        t.email = s.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email)
    VALUES (s.id, s.name, s.email);

この例では、source_table から target_table へのデータをマージしています。id が一致する場合には既存のレコードを更新し、一致しない場合には新しいレコードを挿入します。ON 句では、複数の条件を指定可能です。

MERGE と ON CONFLICT の違い

特徴	ON CONFLICT	MERGE
対応する PostgreSQL バージョン	9.5 以降	15 以降
処理可能な条件数	単一の競合条件のみ	複数の条件や複雑な条件を指定可能
操作の種類	主に挿入と更新（DO UPDATE、DO NOTHING）	挿入、更新、削除など多様な操作が可能

ON CONFLICT はシンプルな UPSERT 操作に最適ですが、より複雑なシナリオでは MERGE ステートメントが適しています。

3. まとめ

PostgreSQL では、UPSERT 操作を実現するために ON CONFLICT 句を使用した INSERT 文と、SQL 標準の MERGE ステートメントの2つの主要な方法が提供されています。ON CONFLICT はシンプルな競合処理に適しており、MERGE は複数の競合条件や複雑な処理を必要とする場合に有用です。

結論として、複数の競合条件を指定したい場合は、MERGE ステートメントを使用することを推奨します。 これにより、柔軟かつ効率的にデータの挿入や更新を管理することが可能となります。

参考資料

• PostgreSQL Documentation - INSERT
• PostgreSQL Documentation - MERGE