PostgreSQL / データの挿入と変更

PostgreSQLで`INSERT ON CONFLICT`を使用してデータをupsertする方法

コンテンツ

はじめに
INSERT...ON CONFLICT構文の使用方法
DO NOTHINGアクションの使用
DO UPDATEアクションの使用
結論

はじめに

PostgreSQLでは、レコードがすでに存在するかどうかに応じて、テーブル内のレコードを追加または変更できます。これは一般に「upsert」操作（「insert」と「update」のかばん語）として知られています。

PostgreSQL内の実際の実装では、特別なON CONFLICT句を持つINSERTコマンドを使用して、レコードがすでにテーブル内に存在する場合の処理を指定します。テーブルにすでにレコードが見つかった場合に、レコードを更新するか、サイレントにスキップするかを指定できます。

`INSERT...ON CONFLICT`構文の使用方法

insertまたはupdate操作の基本的な構文は次のようになります。

INSERT INTO my_table (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8)
ON CONFLICT <target> <action>;

このコンテキストでは、<target>は、ポリシーを定義する競合を指定します。これは次のいずれかになります。

特定の列の名前：(column1)
一意制約の名前：ON CONSTRAINT <constraint_name>

コンパニオン<action>アイテムは、競合が発生した場合にPostgreSQLが実行する内容を定義します。<action>で指定できるのは、次のいずれかです。

DO NOTHING：競合するレコードをそのままにするようにPostgreSQLに指示します。本質的に、このアクションは変更を行いませんが、条件に違反する行を挿入しようとした場合に通常発生するエラーを抑制します。
DO UPDATE：これにより、テーブルにすでにある行を更新するようにPostgreSQLに指示します。updateの構文は、通常のUPDATEコマンドの構文を反映しています。

DO UPDATEが指定されている場合、UPDATE句内で使用できるEXCLUDEDと呼ばれる特別な仮想テーブルが利用可能になります。テーブルには、元のINSERTコマンド（既存のテーブル値と競合した）で提案された値が含まれています。

注意：Prisma Clientを使用してデータベースに接続している場合は、専用のupsert操作を使用してupsert操作を実行できます。

`DO NOTHING`アクションの使用

例として、directorというテーブルがあると仮定します。

テーブルを作成および移入するコマンドを表示するには展開してください

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    latest_film TEXT
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

PostgreSQLが通常、提案された行が既存のデータと競合する挿入をどのように処理するかを見てみましょう。idが3のディレクターがすでに存在すると仮定すると、PostgreSQLはエラーをスローします。

INSERT INTO director (id, name)
VALUES
    (3, 'susan'),
    (4, 'delores');

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

この場合、最初のレコードのみが競合していたとしても、提案されたレコードはいずれも追加されませんでした。競合のない行を追加し続ける場合は、ON CONFLICT DO NOTHING句を使用できます。

ここでは、競合が発生した場合にPostgreSQLに続行するように指示し、他の行の処理を続行します。

INSERT INTO director (id, name)
VALUES
    (3, 'susan'),
    (4, 'delores')
ON CONFLICT (id) DO NOTHING;

INSERT 0 1

テーブルをクエリすると、最初のレコードが既存のレコードの1つと競合した場合でも、2番目のレコードが追加されたことが示されます。

SELECT * FROM director;

 id |  name   | latest_film
----+---------+--------------
  3 | sue     |
  1 | frank   | second movie
  2 | bob     | fourth movie
  4 | delores |
(4 rows)

`DO UPDATE`アクションの使用

代わりに、テーブルにすでに存在する行を更新する場合は、ON CONFLICT DO UPDATE句を使用できます。

ここでは、以前と同じタイプのクエリを実行しますが、今回は、競合が発生した場合に既存のレコードを更新します。

INSERT INTO director (id, name)
VALUES
    (2, 'robert'),
    (5, 'sheila'),
    (6, 'flora')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name;

INSERT 0 3

今回は、提案された挿入の1つと競合する場合に、既存の行に行う変更を指定します。挿入する予定だったアイテムを含む仮想EXCLUDEDテーブルを使用して、競合時にname列を新しい値に更新します。

レコードがすべて更新または追加されたことを表示するには、次のように入力します。

SELECT * FROM director;

 id |  name   | latest_film
----+---------+--------------
  3 | sue     |
  1 | frank   | second movie
  4 | delores |
  2 | robert  | fourth movie
  5 | sheila  |
  6 | flora   |
(6 rows)

結論

PostgreSQLのINSERT...ON CONFLICT構文を使用すると、提案されたレコードが既存のレコードと競合する場合に、2つのオプションから選択できます。DO NOTHINGとDO UPDATEはどちらも、追加するデータが既存のコンテンツにどのように関連しているかに応じて用途があります。

DO NOTHINGオプションを使用すると、競合する行をサイレントにスキップして、競合しない追加レコードを追加できます。一方、DO UPDATEを選択すると、競合が発生した場合に既存のレコードを条件付きで変更でき、オプションで元の提案された行の値を使用できます。それぞれが役立つ可能性のあるシナリオを理解し、この一般的な形式を使用する方法を学ぶことは、既存のデータセットに新しいデータを追加する際のクエリを簡素化するのに役立ちます。

著者について