PostgreSQL UPSERT 重複時更新or Insertしない

MySQLでon duplicate key updateというinsertしようとしてデータ存在したらupdataする便利な機能あります。
UPSERTといって同じデータあったら（制約ではじかれたら）UPDATEするとか、エラー出さないで終了させるとか。

いちいちpythonとかの条件式で書く必要もなく、SQLでやれるのでスマート。

PyhonでPostgresqlに保存しようとして同じような機能がないかなと思いメモします。

ついでにMySQLだとこんな感じ
MySQL INSERT重複あるならUPDATEかIGNORE

テーブル構造

CREATE TABLE sampletable
(
facility_no character varying(20) NOT NULL,
date_time timestamp with time zone NOT NULL,
error character varying(20),
CONSTRAINT upsert_pk PRIMARY KEY(facility_no, date_time)
);

今回upsert_pkという制約名を付けています。
つけない場合調べましょう。

制約名調べ

SELECT table_name, constraint_name, constraint_type
FROM   information_schema.table_constraints
WHERE  table_name='sampletable';

PGAdminでの表示

今回はupsert_pkとつけたのでこれを使います。

SQLでInsert

upsert.sql

INSERT INTO sampletable VALUES ('machine1','2019-11-15 12:00:00', NULL)
ON CONFLICT ON CONSTRAINT upsert_pkey
DO UPDATE SET error='重複';

1行目は普通のINSERT。
2行目upsert_pkeyのエラー出たら
3行目errorカラムに重複と登録

Insertで重複起きたらError起こらなければいい場合

upsert.sql

INSERT INTO sampletable VALUES ('machine1','2019-11-15 12:00:00', NULL)
ON CONFLICT DO NOTHING;

1行目は普通のINSERT。
2行目エラー出たら何もしない

Pythonで

upsert.py

import psycopg2
from psycopg2  import extras

# accountに情報入れていること想定
connection_info = f'''host={account['hostname']}
        port={account['port']} dbname={account['database']}
        user={account['username']} password={account['password']}'''

conn = psycopg2.connect(connection_info)
cur = conn.cursor()

sql = "INSERT INTO public.sampletable VALUES %s ON CONFLICT DO NOTHING;"
extras.execute_values(cur, sql, insert_list)
conn.commit()
cursor.close()
conn.close()