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

INSERT ON CONFLICT 文は、「存在する場合は更新、存在しない場合は挿入」という UPSERT 操作を可能にし、データ同期や一括インポート時のプライマリキーの競合による書き込み失敗を防ぎます。この機能は、MySQL の REPLACE INTO 文に似ています。このトピックでは、INSERT ON CONFLICT の構文と使用例について説明します。

注意事項

パーティションテーブルの場合、INSERT ON CONFLICT 文は、マイナーカーネルバージョン V6.3.6.1 以降を実行している AnalyticDB for PostgreSQL インスタンスでのみサポートされます。
説明
AnalyticDB for PostgreSQL コンソールのインスタンスの **[基本情報]** ページでマイナーバージョンを確認できます。ご利用のインスタンスが必要なバージョンを満たしていない場合は、インスタンスのマイナーバージョンを更新してください。
この機能は、ヒープテーブルと Beam テーブルでのみサポートされます。一意なインデックスをサポートしない列指向テーブル (AO/AOCS) ではサポートされません。テーブルのストレージエンジンを確認するには、「テーブルが使用しているストレージエンジンを確認する方法」をご参照ください。
単一の INSERT 文に、同じプライマリキーを持つ複数の行を含めることはできません。これは SQL 標準の制約です。

SQL 構文

基本構文

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT [ conflict_target ] conflict_action

完全な構文

[ 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 ] [, ...] ]
    
where conflict_target can be one of the following:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )[ WHERE index_predicate ]
    ON CONSTRAINT constraint_name
    
and conflict_action can be one of the following:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

ON CONFLICT 句は UPSERT 操作を可能にします。この句は、以下で説明する conflict_target と conflict_action で構成されます。

パラメーター

説明

conflict_target

競合ターゲットを指定します。これにより、何が競合と見なされるかが決まります。

conflict_action が DO UPDATE の場合、conflict_target は競合の原因となるプライマリキーまたは一意なインデックスを指定する必要があります。
conflict_action が DO NOTHING の場合、conflict_target は省略できます。

conflict_action

競合が発生したときに実行する操作を指定します。有効な値は次のとおりです。

DO NOTHING：conflict_target で競合が発生した場合、挿入対象の行を破棄します。
DO UPDATE：conflict_target で競合が発生した場合、DO UPDATE SET 句を使用して既存の行を更新します。この句内では、excluded 疑似テーブルを介して挿入対象のデータを参照します。
重要
- SET 句の UPDATE 操作では、分散キーまたはプライマリキーを更新することはできません。
- UPDATE 操作の WHERE 句は、サブクエリをサポートしていません。
- Beam テーブルは部分的な列更新をサポートしていません。DO UPDATE ALL を使用した完全な行更新のみをサポートします。

使用例

データの準備

列 a をプライマリキーとして、t1 という名前のテーブルを作成します。
```
CREATE TABLE t1 (
  a int PRIMARY KEY, 
  b int, 
  c int, 
  d int DEFAULT 0
);
```
(任意) Beam ストレージエンジンを使用していて、競合時に部分的な更新を実行する必要がある場合は、テーブルのアクセスメソッドを heap に変更する必要があります。
```
ALTER TABLE t1 SET ACCESS METHOD heap;
```
プライマリキー a が 0 の行をテーブル t1 に挿入します。
```
INSERT INTO t1 VALUES (0,0,0,0);
```
テーブルをクエリしてデータを表示します。
```
SELECT * FROM t1;
```
結果の例は次のとおりです。
```
a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)
```

標準的な挿入

重複するプライマリキー (a = 0) を持つ行を挿入しようとする標準の INSERT 文は失敗します。

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

操作は次のエラーを返します。

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

単一行の UPSERT

エラーを返さずにプライマリキーの競合を処理するには、ON CONFLICT 句を使用します。

競合時の挿入の無視

プライマリキーの競合が発生したときに挿入操作を無視するには、ON CONFLICT DO NOTHING 句を使用します。

データを挿入します。

INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT DO NOTHING;

テーブルデータを表示します。
```
SELECT * FROM t1;
```
結果の例は次のとおりです。
```
a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)
```

競合時のデータ更新

プライマリキーの競合が発生した場合にデータを上書きするには、ON CONFLICT DO UPDATE 句を使用します。

データを挿入します。
DO UPDATE SET 句では、特別な excluded 疑似テーブルに挿入対象の行の値が含まれています。これらの値を参照して、既存の行を更新できます。
```
INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT (a) 
DO UPDATE SET 
  (b, c, d) = (excluded.b, excluded.c, excluded.d);
```
または、各列を個別に指定することもできます。
```
INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT (a) 
DO UPDATE SET 
  b = excluded.b, c = excluded.c, d = excluded.d;
```
テーブルデータを表示します。
```
SELECT * FROM t1;
```
結果は、テーブル t1 のプライマリキー以外の列が更新されたことを示しています。
```
a | b | c | d
---+---+---+---
 0 | 1 | 1 | 1
(1 row)
```

競合時の部分的な列更新

列のサブセットを更新したり、既存の値に基づいて列を更新したりすることもできます。次の例では、これらのシナリオを示します。

競合処理ロジックが期待どおりに機能することを確認するために、テストデータを更新します。
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
元のデータを新しく挿入されたデータで上書きします。
1. c 列のデータのみを上書きします。挿入文は次のとおりです。
```
INSERT INTO t1 
VALUES (0,3,3,3) 
ON CONFLICT (a) 
DO UPDATE SET 
  c = excluded.c;
```
2. テーブルデータを表示します。
```
SELECT * FROM t1;
```
  結果の例は次のとおりです。
```
a | b | c | d
---+---+---+---
 0 | 1 | 3 | 1
(1 row)
```
元のデータに基づいて更新します。
1. プライマリキーの競合後、c 列の値を 1 増分します。挿入文は次のとおりです。
```
INSERT INTO t1 
VALUES (0,0,1,0) 
ON CONFLICT (a) 
DO UPDATE SET 
  c = t1.c + 1;
```
2. テーブルデータを表示します。
```
SELECT * FROM t1;
```
  結果の例は次のとおりです。
```
a | b | c | d
---+---+---+---
 0 | 1 | 4 | 1
(1 row)
```

競合時のデフォルト値への更新

プライマリキーの競合が発生した場合、列をそのデフォルト値に更新できます。

競合処理ロジックが期待どおりに機能することを確認するために、テストデータを更新します。
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
プライマリキーの競合時に、d 列をデフォルト値に戻します。挿入文は次のとおりです。
```
INSERT INTO t1 
VALUES (0,0,2,2) 
ON CONFLICT (a) 
DO UPDATE SET 
  d = default;
```
テーブルデータを表示します。
```
SELECT * FROM t1;
```
結果の例は次のとおりです。
```
a | b | c | d
---+---+---+---
 0 | 1 | 1 | 0
(1 row)
```

複数行の UPSERT

複数の VALUES 句を使用した UPSERT

2 行のデータを挿入します。プライマリキーが競合する行に対しては何も実行されません。プライマリキーが競合しない行については、データは正常に挿入されます。
1. 競合処理ロジックが期待どおりに機能することを確認するために、テストデータを更新します。
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
2. データを挿入します。
```
INSERT INTO t1 
VALUES (0,2,2,2), (3,3,3,3) 
ON CONFLICT DO NOTHING;
```
3. テーブルデータを表示します。
```
SELECT * FROM t1;
```
  結果の例は次のとおりです。
```
a | b | c | d
---+---+---+---
 3 | 3 | 3 | 3
 0 | 1 | 1 | 1
(2 rows)
```
2 行のデータを挿入します。プライマリキーが競合する行については、データが上書きされます。プライマリキーが競合しない行については、データは正常に挿入されます。
1. データを挿入します。
```
INSERT INTO t1 
VALUES (0,0,0,0), (4,4,4,4) 
ON CONFLICT (a) 
DO UPDATE SET 
  (b, c, d) = (excluded.b, excluded.c, excluded.d);
```
2. テーブルデータを表示します。
```
SELECT * FROM t1;
```
  結果の例は次のとおりです。
```
a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 3 | 3 | 3 | 3
 4 | 4 | 4 | 4
(3 rows)
```

サブクエリからの UPSERT

サブクエリからデータを UPSERT することもできます。競合が発生した場合、既存の行が更新されます。この方法は、テーブルのマージや複雑な INSERT INTO SELECT シナリオに役立ちます。以下に例を示します。

競合処理ロジックが期待どおりに機能することを確認するために、テストデータを更新します。
```
DELETE FROM t1 WHERE a != 0; 

UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
t1 と同じスキーマを持つ t2 という名前のテーブルを作成します。
```
CREATE TABLE t2 (like t1);
```
テーブル t2 に 2 行のデータを挿入します。挿入文は次のとおりです。
```
INSERT INTO t2 
VALUES (0,11,11,11),(2,22,22,22);
```
t2 から t1 にデータを挿入します。プライマリキーの競合が発生した場合、プライマリキー以外の列が上書きされます。
```
INSERT INTO t1 
SELECT * FROM t2 
ON CONFLICT (a) 
DO UPDATE SET 
  (b, c, d) = (excluded.b, excluded.c, excluded.d);
```

テーブルデータを表示します。

SELECT * FROM t1;

結果の例は次のとおりです。

a | b  | c  | d
---+----+----+----
 2 | 22 | 22 | 22
 0 | 11 | 11 | 11
(2 rows)

競合時の Beam テーブルの完全な行更新

Beam ストレージエンジンを使用するテーブルの場合、競合が発生したときに完全な行更新を実行するには、INSERT ON CONFLICT DO UPDATE ALL を使用する必要があります。

列 a をプライマリキーとして、beam_test という名前のテーブルを作成します。文は次のとおりです。
```
CREATE TABLE beam_test (
  a int PRIMARY KEY, 
  b int, 
  c int, 
  d int DEFAULT 0
) USING  beam;
```
beam_test テーブルにデータを挿入します。挿入文は次のとおりです。
```
INSERT INTO beam_test 
VALUES
  (0, 0, 0, 0),
  (1, 1, 1, 1),
  (2, 2, 2, 2);
```

テーブルデータを表示します。

SELECT * FROM beam_test;

結果の例は次のとおりです。

a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 rows)

既存の行と競合するデータを挿入します。

INSERT INTO beam_test 
VALUES(0, 4, 4, 4) 
ON CONFLICT (a)  
DO UPDATE ALL;

テーブルデータを表示します。
```
SELECT * FROM beam_test;
```
次のサンプル結果は、競合する行が更新されたことを示しています。
```
a | b | c | d
---+---+---+---
 0 | 4 | 4 | 4
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 rows)
```

よくある質問

テーブルのストレージエンジンを確認する方法

pg_class および pg_am システムカタログをクエリすることで、テーブルのストレージエンジンを特定できます。次の SQL 文を使用し、schamename.tablename をテーブルの完全修飾名に置き換えてください。

SELECT
c.oid::regclass AS rel,
coalesce(a.amname, 'heap') AS table_am
FROM pg_class c
LEFT JOIN pg_am a ON a.oid = c.relam
WHERE c.oid = 'schamename.tablename'::regclass
AND c.relkind = 'r';

Beam テーブルでの部分的な列更新エラーの解決方法

問題：Beam テーブルで INSERT ON CONFLICT を介して部分的な更新を実行すると、次のエラーで失敗します。

ERROR:  INSERT ON CONFLICT DO UPDATE SET is not supported for beam relations
HINT:  Please use INSERT INTO table VALUES(?,?,...) ON CONFLICT DO UPDATE ALL.

このエラーは、Beam ストレージエンジンが部分的な列更新をサポートしておらず、完全な行更新のみを許可しているために発生します。
解決策：これを修正するには、DO UPDATE SET ... 句を DO UPDATE ALL に置き換えます。これにより、競合する行全体が INSERT 文の値で更新されます。