PostgreSQL: レコードが無いときinsert、有るときupdateするにはon conflictを使う

PostgreSQLでprimary keyに該当するレコードがまだ無いときはinsertし、すでに有る場合はupdateするクエリを紹介します。

MySQLでいうところのINSERT ... ON DUPLICATE KEY UPDATEです。

PostgreSQLで、 レコードが無いときinsert、有るときupdateするにはon conflictを使います:

insert into product (id, name) values (1, 'item 1')
on conflict on constraint product_pk
do update set name='item 1';

ちょっとした説明

productテーブルにはidカラムがあります。このidカラムにはprimary key制約がついていて、その制約名が「product_pk」です。on conflict on constraintは「制約に引っかかったとき」という意味です。ここでは、product_pk制約に引っかかったとき、つまり、insertしようとしてidが重複していたときに、何かするという意味になります。この何かするの部分がdo以降のupdate句の内容になります。そのupdate句ではnameカラムだけ更新するようになっています。したがって、クエリの意味をまとめると、「IDを指定してレコードを追加するんだけど、もしIDがすでに存在するなら、nameだけ更新してね」ということになります。

豆知識

条件に応じてinsertとupdateを使い分ける処理は、俗にupdateとinsertの造語として「upsert」と呼ばれます。

実行例

-- テーブルを作ります。product_pk制約も同時に作れます。
create table product 
  (id serial not null constraint product_pk primary key, 
   name varchar);

-- 初回のinsert
insert into product (id, name) values (1, 'product name')
  on conflict on constraint product_pk
  do update set name='product name';

-- テーブルの中身を確認
select * from product;
--  id |  name
-- ----+--------
--   1 | product name
-- (1 row)

-- 二回目のクエリ
insert into product (id, name) values (1, 'new name')
  on conflict on constraint product_pk
  do update set name='new name';

-- テーブルの中身を確認
select * from product;
--  id |  name
-- ----+--------
--   1 | new name
-- (1 row)

-- エラーにならず、nameだけ変更され、行は増えてないことが分かります。