INSERT ON COFLICTを使用してデータを上書きする - AnalyticDB

このトピックでは、INSERT ON CONFLICTを使用してAnalyticDB for PostgreSQLのデータを上書きする方法について説明します。

INSERT ON CONFLICTステートメントを使用すると、INSERTステートメントを実行して同じ主キーを含む新しい行を挿入するときに、主キーを含む既存の行を更新できます。この機能は、UPSERTまたはINSERT OVERWRITEとも呼ばれます。これは、MySQLのREPLACE INTOステートメントに似ています。

使用上の注意

データを上書きするテーブルは行指向のテーブルである必要があります。列指向テーブルは一意のインデックスをサポートしていないため、テーブルを列指向テーブルにすることはできません。
インスタンスのマイナーバージョンが6.3.6.1以降の場合にのみ、テーブルをパーティションテーブルにできます。インスタンスのマイナーバージョンを更新する方法については、「マイナーエンジンバージョンの更新」をご参照ください。
update SET句の配布キー列または主キー列は更新できません。
UPDATE WHERE句でサブクエリを実行することはできません。
テーブルを更新可能なビューにすることはできません。
insertステートメントに主キーの複数のデータレコードを挿入することはできません。これは、標準のSQL構文に基づく普遍的な制限です。

SQL 構文

overwrite構文は、次のINSERTステートメントに基づいています。

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
The valid value of conflict_target:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )
Valid values of conflict_action:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

データを上書きするには、ON CONFLICT句を追加できます。句は、conflict_targetおよびconflict_actionパラメーターで構成されます。次の表は、各パラメーターの説明です。

項目

説明

conflict_target

conflict_actionが [DO UPDATE] に設定されている場合、競合を定義するために使用される主キー列または一意のインデックス列をconflict_targetとして指定する必要があります。
conflick_actionがDO NOTHINGに設定されている場合は、conflict_targetを省略できます。

conflict_action

競合後に実行するアクション。有効な値：

DO NOTHING: conflict_targetで指定された列でデータの競合が発生した場合、挿入するデータを破棄します。
DO UPDATE: conflict_targetで指定された列でデータの競合が発生した場合、次のUPDATE句に基づいてデータを上書きします。

例

次のステートメントを実行して、t1というテーブルを作成します。テーブルに4つの列を設定し、主キー列として列aを指定します。

CREATE TABLE t1 (a int PRIMARY KEY, b int, c int, d int DEFAULT 0);

次の文を実行して, 列aの値が0であるデータの行を挿入します。

INSERT INTO t1 VALUES (0,0,0,0);

次の文を実行してt1テーブルを照会します。

SELECT * FROM t1;

サンプル結果:

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)

次のステートメントを実行して別の行のデータをt1テーブルに挿入し、列aの挿入値が0の場合、エラーが返されます。

INSERT INTO t1 VALUES (0,1,1,1);

同様のエラーメッセージが返されます。

ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(0) already exists.

上記のエラーメッセージを防ぐには、INSERT ON CONFLICTステートメントを使用します。

競合するデータの挿入を無視するには、次のON CONFLICT DO NOTHING句を追加します。これは、競合するデータが破棄されるシナリオに適用できます。
次の文を実行してデータを挿入します。
```
INSERT INTO t1 VALUES (0,1,1,1) ON CONFLICT DO NOTHING;
```
次の文を実行してt1テーブルを照会します。
```
SELECT * FROM t1;
```
次の結果が返されます。 t1テーブルでは操作は行われません。
```
 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)
```
次のON CONFLICT DO UPDATE句を追加して、非プライマリキー列を更新します。これは、テーブル内のすべての列が上書きされるシナリオに適用できます。
次のいずれかのステートメントを実行してデータを挿入します。
```
INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);
```
```
INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET b = excluded.b, c = excluded.c, d = excluded.d; 
```
DO UPDATE SET句では、excludeを使用して、競合するデータで構成される疑似テーブルを表すことができます。主キーが競合する場合、疑似テーブル内の列値はt1テーブル内の列値を上書きするために参照されます。上記のステートメントでは、挿入されたデータ (0,2、2,2) は、行と4つの列を含むexcludeという名前の疑似テーブルを構成します。 excluded.b, excluded.c, excluded.dを使用して、疑似テーブルの列を参照できます。
次の文を実行してt1テーブルを照会します。
```
SELECT * FROM t1;
```
次の結果が返されます。 t1テーブルの非主キー列が更新されます。
```
 a | b | c | d
---+---+---+---
 0 | 2 | 2 | 2
(1 row)
```

上記のシナリオに加えて、次のシナリオでINSERT ON CONFLICTを使用できます。

主キーの競合が発生したときに、INSERT ON CONFLICTを使用して特定の列のデータを上書きします。これは、競合するデータに基づいて特定の列が上書きされるシナリオに適用できます。
主キーの競合が発生した場合、次のステートメントを実行して列cのデータのみを上書きします。
```
INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET c = excluded.c;
```
次の文を実行してt1テーブルを照会します。
```
SELECT * FROM t1;
```
サンプル結果:
```
 a | b | c | d
---+---+---+---
 0 | 2 | 3 | 2
(1 row)
```
主キーの競合が発生したときに、INSERT ON CONFLICTを使用して特定の列のデータを更新します。これは、特定の列が元のデータに基づいて更新されるシナリオに適用できます。
主キーの競合が発生した場合、次のステートメントを実行して列dのデータに1を追加します。
```
INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET d = t1.d + 1;
```
次の文を実行してt1テーブルを照会します。
```
SELECT * FROM t1;
```
サンプル結果:
```
 a | b | c | d
---+---+---+---
 0 | 2 | 3 | 3
(1 row)
```
主キーの競合が発生したときに、INSERT ON CONFLICTを使用して特定の列をデフォルト値に設定します。
主キーの競合が発生したときに、次のステートメントを実行して列dの値をデフォルト値に設定します。列dのデフォルト値は0です。
```
INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET d = default;
```
次の文を実行してt1テーブルを照会します。
```
SELECT * FROM t1;
```
サンプル結果:
```
 a | b | c | d
---+---+---+---
 0 | 2 | 3 | 0
(1 row)
```
INSERT ON CONFLICTを使用して、複数行のデータを挿入します。
- 次のステートメントを実行して、2行のデータを挿入します。主キーと競合する行については、操作は実行されません。主キーと競合しない行の場合、行は期待どおりに挿入されます。
```
INSERT INTO t1 VALUES (0,0,0,0), (1,1,1,1) ON CONFLICT DO NOTHING;
```
  次の文を実行してt1テーブルを照会します。
```
SELECT * FROM t1;
```
  サンプル結果:
```
 a | b | c | d
---+---+---+---
 0 | 2 | 3 | 0
 1 | 1 | 1 | 1
(2 rows)
```
- 次のステートメントを実行して、2行のデータを挿入します。主キーと競合する行については、データが上書きされます。主キーと競合しない行の場合、行は期待どおりに挿入されます。
```
INSERT INTO t1 VALUES (0,0,0,0), (2,2,2,2) ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);
```
  次の文を実行してt1テーブルを照会します。
```
SELECT * FROM t1;
```
  サンプル結果:
```
 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 rows)
```
主キーの競合が発生したときに元のデータを上書きするためにサブクエリから取得したデータを挿入するには、INSERT ON CONFLICTを使用します。これは、2つのテーブルからのデータのマージや複雑なINSERT INTO SELECTステートメントの実行に適用できます。
次のステートメントを実行して、t1テーブルと同じスキーマを持つt2という名前のテーブルを作成します。
```
CREATE TABLE t2 (like t1);
```
次のステートメントを実行して、2行のデータをt2テーブルに挿入します。
```
INSERT INTO t2 VALUES (2,22,22,22),(3,33,33,33);
```
次のステートメントを実行して、t2テーブルのデータをt1テーブルに挿入します。主キーの競合が発生した場合は、主キー以外の列を上書きします。
```
INSERT INTO t1 SELECT * FROM t2 ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);
```
次の文を実行してt1テーブルを照会します。
```
SELECT * FROM t1;
```
サンプル結果:
```
 a | b  | c  | d
---+----+----+----
 0 |  0 |  0 |  0
 1 |  1 |  1 |  1
 2 | 22 | 22 | 22
 3 | 33 | 33 | 33
(4 rows)
```