「【総まとめ】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 ～を使います。
簡単に説明すると、Insert Into ～にインサート文を記述し、 on conflict ～ に重複キーを指定、do update set に重複があった場合の update 文を記述します。
Insert Into の後に value ではなく select * from テーブル名 を記述すると、２つのテーブル間でUpsertによる同期が行えます。
|
1 2 3 4 5 6 7 8 9 10 11 |
-- table_A に1行 Upsert する場合 insert into table_A values(1,'2023/01/01 09:00:00',10000,15) on conflict (shop_no,order_date,product_id) do update set count=excluded.count -- table_B からtable_A に Upsert する場合 insert into table_A select * from table_B on conflict (shop_no,order_date,product_id) do update set count=excluded.count |
do update set count = excluded.count と記述していますが、これで values の 値を参照しています（詳細は後述）。
Upsertを使うにはユニーク制約が必要
PostgreSQLでUpsertをする上でポイントとなるのがユニーク制約です。
on conflict (キー1,キー2,・・・）で指定したキーの組み合わせで Insert か Update を判断するため、必ずユニークである必要があります。
通常はCreate文でプライマリキーを指定しますので、これがユニーク制約の役割を果たしてくれます。しかし、プライマリキーと異なるキーでUpsertを行う場合、ユニークであることを保証するために別途ユニーク制約を付与する必要があります。
|
1 2 3 4 5 6 7 8 9 |
--プライマリキーとは異なるキーでUpsertする場合、ユニーク制約が必要 create table mydata ( s_id integer not null, p_id integer not null, name varchar(50), primary key (s_id) --プライマリキー unique(s_id,p_id) --ユニーク制約 ); |
上記の動作は PostgresSQL Ver14 で確認していますが、バージョンによって挙動が異なる可能性があります。
もしお使いの環境で「ON CONFLICT 指定に合致するユニーク制約または排除制約がありません」というエラーが表示された場合、ユニーク制約を付けてみてください。
既に存在するテーブルに対して、後からユニーク制約を付ける場合は alter table 文を使います。
＜ユニーク制約名を指定しない場合＞
alter table テーブル名 add unique(キー1,キー2,・・・)
＜ユニーク制約名を指定する方法＞
alter table テーブル名 add constraint ユニーク制約名 unique(キー1,キー2,・・・)
|
1 2 |
alter table mydata add unique(id) alter table mydata add constraint mydata_unique_keys unique(id) |
後ほど詳しく説明しますが、ユニーク制約に名前を付けると、少しだけ記述が簡単になります。
検証データ
本記事のUpsert文を実際に試してみたい場合は、以下のSQLを実行して下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--サンプルテーブルの作成とテストデータ登録 create table oders ( shop_no integer not null, order_date timestamp not null, product_id integer not null, count integer not null, constraint oders_p_key primary key (shop_no,order_date,product_id) ); alter table oders add constraint oders_uniquekeys unique(shop_no,order_date,product_id) insert into oders values(1,'2023/01/01 09:00:00',10000,5); insert into oders values(1,'2023/01/01 09:10:00',10000,15); insert into oders values(1,'2023/01/01 09:20:00',10000,25); --下記はテーブル間のUpsert確認用(odersを丸ごとコピー） create table old_oders as select * from oders |
1行をUpsertする
下記は、サンプルテーブルに (1,'2023/01/01 09:00:00',10000,15) のデータをインサートし、レコードが既に存在していれば 数量(count)を 15に更新するサンプルです。
|
1 2 3 |
insert into oders values(1,'2023/01/01 09:00:00',10000,15) on conflict (shop_no,order_date,product_id) do update set count=15 |
プライマリキー名、又はユニーク制約名を使って conflict を簡略化する
今回は、プライマリキーに oders_p_key 、ユニーク制約に oders_uniquekeys という名前を付けています。
こうすることで、on conflict の部分が次のように簡略化できます。
on conflict on constraint プライマリキー名 又は ユニーク制約名
具体的には、次のようになります。
|
1 2 3 4 5 6 7 8 9 |
-- プライマリキー名を指定した例 insert into oders values(1,'2023/01/01 09:00:00',10000,5) on conflict on constraint oders_p_key do update set count=5 -- ユニーク制約名を指定した例 insert into oders values(1,'2023/01/01 09:00:00',10000,5) on conflict on constraint oders_uniquekeys do update set count=5 |
EXCLUDEDを使って、update set を簡略化する
Insert 文で指定した values の値は、EXCLUDEDという一時的なテーブル（のようなもの）に格納されており、これを使って参照が可能です。
今までは update 文に直接値を指定していましたが、 EXCLUDED.カラム名 で更新が行えます。
|
1 2 3 |
insert into oders values(1,'2023/01/01 09:00:00',10000,15) on conflict (shop_no,order_date,product_id) do update set count=excluded.count |
複数行をまとめてUpsertする
PostgreSQLでは、1回のInsert文で複数行（複数レコード）をまとめて登録する マルチインサート文が使えますが、このマルチインサート文に続けて ON CONFLICT UPDATE SET ～ と記述することで、複数行をまとめてUpsertできます。
|
1 2 3 4 5 6 |
insert into oders values (1,'2023/01/01 09:01:00',10000,15), (1,'2023/01/01 09:02:00',10100,12), (1,'2023/01/01 09:03:00',10200,18) on conflict (shop_no,order_date,product_id) do update set count=excluded.count |
ポイントは、do update set ～ の部分で、excluded を使ってUpdateの値を指定するところ（今回の例では count=excluded.count と記述している箇所）です。
count = 5 など、直接値を指定するとエラーになりますので、必ず excluded を指定して下さい。
また、1回のUpsertの中で、キーが重複するデータが存在するとエラーになります。
テーブル間でUpsertする
２つのテーブルAとBがあって、Bの内容をAにUpsertしたい場合、select * from を使います。
insert into テーブル名A select * from テーブル名B
下記は old_oders から oders へ Upsertをする例です。
|
1 2 3 |
insert into oders select * from old_oders on conflict (shop_no,order_date,product_id) do update set count=excluded.count |
ユニーク制約名を使えば、次のようにも書けます。
|
1 2 3 |
insert into oders select * from old_oders on conflict on constraint oders_uniquekeys do update set count=excluded.count |
UpsertでInsertだけ行う（Updateしない）
なければインサート、あれば何もしたくない場合は、do に続けて NOTING を記述します。
|
1 2 3 4 5 6 7 8 9 10 |
-- oders に向けてインサートを行うが、既に存在する場合は何もしない insert into oders values(1,'2023/01/01 09:00:00',10000,15) on conflict (shop_no,order_date,product_id) do nothing -- old_odersから oders に向けてインサートを行うが、既に存在するレコードには何もしない insert into oders select * from old_oders on conflict (shop_no,order_date,product_id) do nothing |
Pythonのサンプル
それでは、参考例としてDataFrameの内容からUpsert文を生成するPythonのサンプルプログラムをご紹介します。
使い方のサンプル
create_upsert にDataFrame、テーブル名、ユニークキーのリストを指定するだけです。
尚、下記サンプルで生成したUpsert文は、最初に紹介したサンプルテーブルに対して実行が可能です。
|
1 2 3 4 5 6 7 8 9 10 |
datas = [ [1,'2023/01/01 09:00:00',10000,55], [1,'2023/01/01 09:10:00',10000,65], [1,'2023/01/01 09:20:00',10000,75] ] df = pd.DataFrame(datas,columns=['shop_no','order_date','product_id','count']) sqls = create_upsert(df,'oders',['shop_no','order_date','product_id']) for sql in sqls: print(sql) |
関数のソースコード
以下は、関数本体のソースコードです。数値以外のデータ（文字列、日付）はシングルクォートで括る必要があるため、dtype が 'object' か否かで判断しています。
また、insert into ～ values ～ の values に渡すカラム値は、文字列に変換したのち、'nan'⇒NULL に置換しています。このため、カラム値に 'nan' が含まれるとNULLに変換されてしまうのでご注意下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
import pandas as pd def create_upsert(df,table,key_columns): ''' DataFrameの内容からUpser文を作成する Parameters ---------- df : DataFrame データを格納したDataFrame table : str Upsert対象のテーブル名 key_columns : list of str ユニークキーのカラム 例:['shop_no','order_date',・・・]" Returns ---------- res:str 生成されたUpsertインサート文 ''' # df から全てのカラム名を取り出す columns = ",".join([i for i in df.columns]) # シングルクォート格納用のリストを作り、カラムの型がobjectの場合のみシングルクォートをセット quote = ["'" if str(dtype) == 'object' else '' for dtype in df.dtypes] sqls = [] # 全ての行を取り出す for row in df.itertuples(): # 0カラム名（行インデックス）を飛ばし、必要に応じて各カラム値をシングルクォートで括る。 items = [quote[i] + str(row[i+1]) + quote[i] for i in range(len(df.columns))] # Insert句の作成 values = ','.join(items).replace("'nan'", "NULL") insert = f'insert into {table}({columns}) values({values}) ' # conflict句の作成 keys = ','.join(key_columns) conflict = f'on conflict ({keys}) ' # update句の作成 set_list = [f'{x}=excluded.{x}' for x in df.columns if x not in key_columns] update = f"do update set " + ','.join(set_list) # insert、conflict、updateを結合 sqls.append(insert + conflict + update) return sqls |
まとめ
今回はPostgreSQLのUpsertに焦点を絞り、深堀しました。
PostgreSQLでUpsertを行うには、 Insert into ～ にインサート文を、on conflict ～ に重複を判断するためのユニークキー（又はキー名）を、do ～ に update 文を記述します。
もし、存在した場合に何もしなくない場合は、do nothing と記述します。
また、excluded に これからインサートしようとする値が格納されていて、 excluded.カラム名で参照できることも紹介しました。
PostgreSQLでUpsertが必要になった時は、是非この記事を参考にしてください。
コメント