【詳しく解説】PostgreSQLにおけるUpsertの書き方(サンプル付き)

当ページのリンクには広告が含まれています。

【総まとめ】UPSERTとは?DBごとの違いは?」では、主要なDBごとの書き方をざっくり説明しました。

今回は、PostgreSQLでのUpsertに焦点を絞り、さらに深堀していきたいと思います。

参考までに、DataFrameからUpsertを生成するPython関数も紹介していますので、必要に応じてコピぺでご利用下さい。

尚、PostgreSQL Ver 15 からは SQL標準の MERGE 文が使えるようになりますが、普及にはまだまだ時間が掛かるため、この記事では on conflict ~を使った説明を行っています。

目次

PosgreSQLは Insert into ~ on conflict ~ で Upsertを行う

PostgreSQLでUpsertを行う場合は、Insert into ~ on confrict ~を使います。

UPSERT文の構造を表す画像

簡単に説明すると、Insert Into ~にインサート文を記述し、 on conflict ~ に重複キーを指定、do update set に重複があった場合の update 文を記述します。

Insert Into の後に value ではなく select * from テーブル名 を記述すると、2つのテーブル間でUpsertによる同期が行えます。 

do update set count = excluded.count と記述していますが、これで values の 値を参照しています(詳細は後述)。

Upsertを使うにはユニーク制約が必要

ユニーク制約のサンプル画像

PostgreSQLでUpsertをする上でポイントとなるのがユニーク制約です。

on conflict (キー1,キー2,・・・)で指定したキーの組み合わせで Insert か Update を判断するため、必ずユニークである必要があります。

通常はCreate文でプライマリキーを指定しますので、これがユニーク制約の役割を果たしてくれます。しかし、プライマリキーと異なるキーでUpsertを行う場合、ユニークであることを保証するために別途ユニーク制約を付与する必要があります。

上記の動作は PostgresSQL Ver14 で確認していますが、バージョンによって挙動が異なる可能性があります。

もしお使いの環境で「ON CONFLICT 指定に合致するユニーク制約または排除制約がありません」というエラーが表示された場合、ユニーク制約を付けてみてください。

「ON CONFLICT 指定に合致するユニーク制約または排除制約がありません」のエラー画面

 

既に存在するテーブルに対して、後からユニーク制約を付ける場合は alter table 文を使います。

<ユニーク制約名を指定しない場合>
alter table テーブル名 add unique(キー1,キー2,・・・)

<ユニーク制約名を指定する方法>
alter table テーブル名 add constraint ユニーク制約名 unique(キー1,キー2,・・・)

後ほど詳しく説明しますが、ユニーク制約に名前を付けると、少しだけ記述が簡単になります。

検証データ

本記事のUpsert文を実際に試してみたい場合は、以下のSQLを実行して下さい。

1行をUpsertする

1レコードのUpsertイメージ図

下記は、サンプルテーブルに (1,'2023/01/01 09:00:00',10000,15) のデータをインサートし、レコードが既に存在していれば 数量(count)を 15に更新するサンプルです。

プライマリキー名、又はユニーク制約名を使って conflict を簡略化する

今回は、プライマリキーに oders_p_key 、ユニーク制約に oders_uniquekeys という名前を付けています。

こうすることで、on conflict の部分が次のように簡略化できます。

   on conflict on constraint プライマリキー名 又は ユニーク制約名

具体的には、次のようになります。

EXCLUDEDを使って、update set を簡略化する

Insert 文で指定した values の値は、EXCLUDEDという一時的なテーブル(のようなもの)に格納されており、これを使って参照が可能です。

EXCLUDEDのイメージ図

今までは update 文に直接値を指定していましたが、 EXCLUDED.カラム名 で更新が行えます。

複数行をまとめてUpsertする

PostgreSQLでは、1回のInsert文で複数行(複数レコード)をまとめて登録する マルチインサート文が使えますが、このマルチインサート文に続けて ON CONFLICT UPDATE SET ~ と記述することで、複数行をまとめてUpsertできます。

ポイントは、do update set ~ の部分で、excluded を使ってUpdateの値を指定するところ(今回の例では count=excluded.count と記述している箇所)です。

count = 5 など、直接値を指定するとエラーになりますので、必ず excluded を指定して下さい。

また、1回のUpsertの中で、キーが重複するデータが存在するとエラーになります。

1回のInsert文でキーが重複している例の画像

テーブル間でUpsertする

2つのテーブル同士のUpsertイメージ図

2つのテーブルAとBがあって、Bの内容をAにUpsertしたい場合、select * from を使います。

insert into テーブル名A select * from テーブル名B

下記は old_oders から oders へ Upsertをする例です。

ユニーク制約名を使えば、次のようにも書けます。

UpsertでInsertだけ行う(Updateしない)

Upsertでなければインサート、あればなのもしない動作のイメージ画像

なければインサート、あれば何もしたくない場合は、do に続けて NOTING を記述します。

Pythonのサンプル

それでは、参考例としてDataFrameの内容からUpsert文を生成するPythonのサンプルプログラムをご紹介します。

使い方のサンプル

create_upsert にDataFrame、テーブル名、ユニークキーのリストを指定するだけです。

尚、下記サンプルで生成したUpsert文は、最初に紹介したサンプルテーブルに対して実行が可能です。

関数のソースコード

以下は、関数本体のソースコードです。数値以外のデータ(文字列、日付)はシングルクォートで括る必要があるため、dtype が 'object' か否かで判断しています。

また、insert into ~ values ~ の values に渡すカラム値は、文字列に変換したのち、'nan'⇒NULL に置換しています。このため、カラム値に 'nan' が含まれるとNULLに変換されてしまうのでご注意下さい。

まとめ

今回はPostgreSQLのUpsertに焦点を絞り、深堀しました。

PostgreSQLでUpsertを行うには、 Insert into ~ にインサート文を、on conflict ~ に重複を判断するためのユニークキー(又はキー名)を、do ~ に update 文を記述します。

もし、存在した場合に何もしなくない場合は、do nothing と記述します。

また、excluded に これからインサートしようとする値が格納されていて、 excluded.カラム名で参照できることも紹介しました。

PostgreSQLでUpsertが必要になった時は、是非この記事を参考にしてください。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次