【実用】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 ～を使います。

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

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

-- oders に1行登録する場合（例：oders に１行インサートする）
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

-- ２つのテーブル間で同期を取る場合 （例：old_oders ⇒ odersにUpsertする)
insert into oders
select * from old_oders
on conflict (shop_no,order_date,product_id)

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

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

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

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

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

--プライマリキーとは異なるキーで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 指定に合致するユニーク制約または排除制約がありません」というエラーが表示された場合、ユニーク制約を付けてみてください。

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

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

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

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

alter table mydata add unique(id)
alter table mydata add constraint mydata_unique_keys unique(id)

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

検証データ

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

--サンプルテーブルの作成とテストデータ登録
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確認用
create table old_oders as select * from oders

1行をUpsertする

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

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 プライマリキー名又はユニーク制約名

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

-- プライマリキー名を指定した例
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.カラム名で更新が行えます。

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する

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

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

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

insert into oders select * from old_oders
on conflict (shop_no,order_date,product_id)
do update set count=excluded.count

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

insert into oders select * from old_oders
on conflict on constraint oders_uniquekeys
do update set count=excluded.count

UpsertでInsertだけ行う（Updateしない）

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

-- 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文は、最初に紹介したサンプルテーブルに対して実行が可能です。

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に変換されてしまうのでご注意下さい。

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
        生成されたマルチテーブルインサート文  
    '''
    # 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が必要になった時は、是非この記事を参考にしてください。

TechMedia

【実用】PostgreSQLで使うUpsertの書き方と注意点

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

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

検証データ

1行をUpsertする

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

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

テーブル間でUpsertする

UpsertでInsertだけ行う（Updateしない）

Pythonのサンプル

使い方のサンプル

関数のソースコード

まとめ

Archives

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

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

検証データ

1行をUpsertする

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

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

テーブル間でUpsertする

UpsertでInsertだけ行う（Updateしない）

Pythonのサンプル

使い方のサンプル

関数のソースコード

まとめ

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