PostgreSQLにおけるUPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE)の解説

UPSERTとは、データベースのレコードを更新する際に、存在すれば更新し、存在しなければ挿入する操作のことです。PostgreSQLでは、この操作を効率的に行うために、INSERT ... ON DUPLICATE KEY UPDATEステートメントを使用します。

具体的な構文

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (unique_constraint_name)
DO UPDATE SET
    column1 = EXCLUDED.column1,
    column2 = EXCLUDED.column2,
    ...;

• table_name: 挿入または更新するテーブルの名前です。
• column1, column2, ...: 挿入または更新する列の名前です。
• value1, value2, ...: 挿入または更新する値です。
• unique_constraint_name: 衝突を検出するためのユニーク制約の名前です。
• EXCLUDED: 衝突が発生した場合に挿入しようとした行の値を表すエイリアスです。

動作原理

1. 指定されたテーブルに新しいレコードを挿入しようとします。
2. 挿入しようとしたレコードがユニーク制約に違反した場合、衝突が発生します。
3. 衝突が発生した場合、ON CONFLICT句で指定された更新操作が実行されます。
4. 更新操作では、EXCLUDEDエイリアスを使用して、衝突したレコードの値を新しい値に更新します。

例

CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL, email TEXT NOT NULL );

INSERT INTO users (name, email)
VALUES ('Alice', '[email protected]'),
       ('Bob', '[email protected]'),
       ('Charlie', '[email protected]'),
       ('Alice', '[email protected]') -- 重複する名前
ON CONFLICT (name)
DO UPDATE SET
    email = EXCLUDED.email;

この例では、新しいユーザーレコードを挿入します。名前が重複する場合は、既存のレコードのメールアドレスを更新します。

基本的なUPSERT操作

INSERT INTO users (name, email)
VALUES ('Alice', '[email protected]'),
       ('Bob', '[email protected]'),
       ('Charlie', '[email protected]'),
       ('Alice', '[email protected]') -- 重複する名前
ON CONFLICT (name)
DO UPDATE SET
    email = EXCLUDED.email;

複数の列での衝突検出

INSERT INTO products (product_id, name, price)
VALUES (1, 'Apple', 1.00),
       (2, 'Banana', 0.50),
       (3, 'Orange', 0.75),
       (1, 'Apple', 1.25) -- 重複する product_id
ON CONFLICT (product_id, name)
DO UPDATE SET
    price = EXCLUDED.price;

• 説明:
  • productsテーブルに新しい商品レコードを挿入します。
  • product_idとnameが両方重複した場合、価格を更新します。

複数の列での更新

INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (100, 1, 100.00),
       (200, 2, 50.00),
       (300, 1, 80.00),
       (100, 1, 120.00) -- 重複する order_idとcustomer_id
ON CONFLICT (order_id, customer_id)
DO UPDATE SET
    total_amount = total_amount + EXCLUDED.total_amount;

• 説明:
  • order_idとcustomer_idが両方重複した場合、注文の合計金額を更新します。

複数の更新条件

INSERT INTO inventory (product_id, quantity)
VALUES (1, 10),
       (2, 20),
       (1, 5) -- 重複する product_id
ON CONFLICT (product_id)
DO UPDATE SET
    quantity = quantity + EXCLUDED.quantity
WHERE inventory.quantity < 100;

• 説明:
  • product_idが重複した場合、在庫量を更新しますが、現在の在庫量が100未満の場合のみ更新します。

PostgreSQLにおけるUPSERTの代替手法

UPSERTは、データベースのレコードを更新する際に、存在すれば更新し、存在しなければ挿入する操作のことです。PostgreSQLでは、INSERT ... ON CONFLICTステートメントを使用するのが一般的ですが、他にも代替手法が存在します。

MERGEステートメント (PostgreSQL 14以降)

PostgreSQL 14以降では、MERGEステートメントを使用することができます。これは、UPSERT操作をより直感的かつ柔軟に表現するための構文です。

MERGE INTO target_table AS t USING source_table AS s ON t.column = s.column WHEN MATCHED THEN UPDATE SET t.column1 = s.column1, ... WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (s.column1, s.column2   , ...);

• 説明:
  • target_table: 更新または挿入するターゲットテーブルです。
  • ON句: 2つのテーブル間の結合条件を指定します。
  • WHEN MATCHED句: 2つのテーブルのレコードが一致した場合の更新操作を指定します。

プログラミング言語による実装

アプリケーション層でUPSERTロジックを実装することも可能です。これは、データベース固有の機能に依存しないため、移植性が向上します。ただし、パフォーマンスは低下する可能性があります。

import psycopg2

conn = psycopg2.connect(...)
cur = conn.cursor()

data = [
    ('Alice', '[email protected]'),
    ('Bob', '[email protected]'),
    ('Charlie', '[email protected]'),
    ('Alice', '[email protected]')
]

for name, email in data:
    cur.execute("SELECT id FROM users WHERE name = %s", (name,))
    row = cur.fetchone()
    if row:
        cur.execute("UPDATE users SET email = %s WHERE id = %s", (email, row[0]))
    else:
        cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (name, email))

conn.commit()
cur.close()
conn.close()

• 説明:
  • Pythonのpsycopg2モジュールを使用してPostgreSQLに接続します。
  • データをループ処理し、レコードが存在すれば更新し、存在しなければ挿入します。

ストアドプロシージャの使用

ストアドプロシージャを使用することで、UPSERTロジックをデータベース内にカプセル化することができます。これは、パフォーマンスと管理性を向上させることができます。

CREATE OR REPLACE PROCEDURE upsert_user(name TEXT, email TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET email = email WHERE name = name;
    IF NOT FOUND THEN
        INSERT INTO users (name, email) VALUES (name, email);
    END IF;
END;
$$;

• 説明:
  • ストアドプロシージャを作成し、UPSERTロジックを実装します。
  • プログラムからストアドプロシージャを呼び出すことで、UPSERT操作を実行します。