PostgreSQL / データの挿入と変更

PostgreSQLでデータをupsertするために`INSERT ON CONFLICT`を使用する方法

コンテンツ

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

共有する

はじめに

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

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

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

挿入または更新操作の基本的な構文は次のようになります

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コマンドの構文を反映しています。

DO UPDATEが指定されている場合、EXCLUDEDという特別な仮想テーブルがUPDATE句内で使用できます。このテーブルには、元の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

今回は、提案された挿入のいずれかと競合する場合に、既存の行に行う変更を指定します。仮想テーブル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オプションでは、競合が発生した場合に既存のレコードを条件付きで変更でき、必要に応じて元の提案された行の値を使用できます。それぞれの用途が役立つシナリオを理解し、この一般的な形式の使用方法を学ぶことは、既存のデータセットに新しいデータを追加する際のクエリを簡素化するのに役立ちます。

著者について