PostgreSQL で UPSERT
@nomu487495|2024-11-14 (4w ago)2 views
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', 'alice@example.com')
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', 'foo@example.com', '012345678');
INSERT INTO users (username, email)
VALUES ('hogehoge', 'hogehoge@example.com', '012345678')
ON CONFLICT (email)
DO UPDATE; -- phone_number のユニーク制約により、エラーが起きる
INSERT INTO users (username, email)
VALUES ('hogehoge', 'hogehoge@example.com', '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
ステートメントを使用することを推奨します。 これにより、柔軟かつ効率的にデータの挿入や更新を管理することが可能となります。