PostgreSQL が存在する場合は挿入または更新

キーが新しい場合は行を挿入し、キーがすでに存在する場合は更新するには、PostgreSQL の INSERT ... ON CONFLICT（UPSERT）を使います。競合対象は、主キーなどの一意制約または一意インデックスに一致している必要があります。たとえば、id が主キーの users テーブルで、INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; を実行すると、ユーザー 12 が挿入されるか、既存の行の 3 つのフィールドが置き換えられます。

以下の例では、次のスキーマを前提とします。CREATE TABLE users (id integer PRIMARY KEY, name text NOT NULL, gender text NOT NULL, age integer NOT NULL);。SQL の例は PostgreSQL サーバーが必要なため本文中に示しています。Python の例は、挿入か更新かを判断する流れを簡単にモデル化したものです。

次の例を実行します。

users = {}

def upsert(user):
    key = user["id"]
    if key in users:
        users[key].update(user)
    else:
        users[key] = user.copy()

upsert({"id": 12, "name": "John Kane", "age": 23})
upsert({"id": 12, "name": "John Kane", "age": 24})
print(users[12])
print("rows:", len(users))

{'id': 12, 'name': 'John Kane', 'age': 24}
rows: 1

この Python のコードは判断の流れだけを示しており、PostgreSQL に接続したり SQL を実行したりするものではありません。以下の SQL パターンはデータベース上での例なので、記載したスキーマのテーブルに対して実行してください。出力ブロックの rows: というラベルは、変更されていない英語表記のままです。

UPDATEしてからINSERTする方法で、行が存在する場合に挿入または更新する

更新の後に条件付きで挿入する方法は、2つの文で構成されます。まず UPDATE users SET name = 'John Kane', gender = 'Male', age = 21 WHERE id = 12 RETURNING id; を実行します。行が返された場合は、既存のユーザーが更新されています。行が返されなければ、INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 21); を実行します。

これらの文を発行するアプリケーションまたはトランザクションは、UPDATE ... RETURNING の結果に基づいて INSERT を実行するかどうか判断する必要があります。UPDATE と INSERT を別々に実行しても、アトミックな UPSERT にはなりません。その間に別のトランザクションが同じキーを挿入すると、一意キーの競合が発生する可能性があります。通常の挿入・更新処理で同時書き込みに対応する必要がある場合は、以下の INSERT ... ON CONFLICT を使ってください。

INSERT ... ON CONFLICT（UPSERT）を使って、行が存在する場合に挿入または更新する

INSERT ... ON CONFLICT は挿入を試み、一意性の競合が起きた場合の処理も同じ文の中で行います。競合対象には、利用可能な一意制約または一意インデックスを指定する必要があります。この例では id が主キーで、EXCLUDED は挿入しようとした行を指します。

INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; を使うと、行を挿入するか、id = 12 の行がすでに存在する場合はその名前、性別、年齢を置き換えられます。変更するフィールドを選びたい場合は、DO UPDATE SET の後に対象のフィールドだけを指定します。たとえば DO UPDATE SET age = EXCLUDED.age とすれば、既存の名前と性別は保持されます。

処理結果は指定したアクションによって決まります。新しいキーなら指定した行を挿入し、既存のキーなら DO UPDATE SET に列挙したフィールドを更新します。競合時に既存の行を変更しない場合は、DO NOTHING も指定できます。コミュニティによって管理されている背景情報については、PostgreSQL Wiki の UPSERT ページを参照してください。

共通テーブル式（CTE）を使って、行が存在する場合に挿入または更新する

データを変更する CTE を使うと、UPDATE の RETURNING が返す行を条件付きの INSERT につなげられます。たとえば、WITH updated AS (UPDATE users SET name = 'John Kane', gender = 'Male', age = 25 WHERE id = 12 RETURNING id) INSERT INTO users (id, name, gender, age) SELECT 12, 'John Kane', 'Male', 25 WHERE NOT EXISTS (SELECT 1 FROM updated); のように記述します。

この文では、UPDATE が行を返さなかった場合にだけ INSERT が実行されます。更新と代わりの挿入を1つの SQL 文にまとめたい場合に便利ですが、同時書き込み時の ON CONFLICT の代わりにはなりません。別のトランザクションによる挿入で一意制約違反が起きる可能性があるため、通常の競合に安全な UPSERT が必要な場合は INSERT ... ON CONFLICT を使ってください。

PL/pgSQL 関数を使って、行が存在する場合に挿入または更新する

PL/pgSQL 関数を使うと、推奨する競合処理の文を再利用できるようにまとめられます。列名と混同しないパラメーター名を付けてください。たとえば、p_id integer、p_name text、p_gender text、p_age integer をパラメーターとして受け取り、関数本体で INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; を実行できます。

CREATE OR REPLACE FUNCTION upsert_user(p_id integer, p_name text, p_gender text, p_age integer) RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; END; $$; で関数を定義し、SELECT upsert_user(13, 'New User', 'Female', 30); で呼び出します。これにより、同じ一意キーの競合処理をデータベースに任せられます。関数を使っても、UPDATE の後に INSERT を別途行う場合の競合リスクが生じるわけではありません。

まとめ

通常の条件付き書き込みでは、主キーなど適切な一意制約を競合対象として INSERT ... ON CONFLICT（UPSERT）を使います。新しい行を挿入し、そのキーがすでに存在する場合は選択したフィールドを適用します。UPDATE の後に INSERT する方法や、更新後に条件付きで挿入する CTE は、2つの結果を示す例としては使えますが、同時書き込み時に同じ競合処理を提供するものではありません。データベース内で競合処理を再利用したい場合は、PL/pgSQL 関数を使ってください。