PostgreSQLでbulk insert/update/upsertしたいときのメモ
実際に動かしてみての細かい確認は以下に記載
PostgreSQLでbulk insert/update/upsert - yu/logs/*
0.確認用の環境
0.1.前提：PostgreSQLのバージョン
PostgreSQL9.6(SQL Fiddle で確認)
0.2.確認用のテーブル
適当に簡単なテーブルを作成
create table test_table (
id integer not null primary key
, name varchar(100)
, note varchar(255)
);
1.bulk insert
insert into test_table (id, name, note)
values
(1, 'one', '壱')
, (2, 'two', '弐')
, (3, 'three', '参')
;
2.bulk update
- 実行前のテーブルの状態は、前述のbulk insert実施後を想定
パターン1 case式で分岐させる
update test_table
set
name = case id
when 1 then 'one_upd'
when 2 then 'two_upd'
when 3 then 'three_upd'
end
, note = case id
when 1 then 'ichi'
when 2 then 'ni'
when 3 then 'san'
end
where
id in (1, 2, 3)
;
パターン2 各値をパラメータとして渡す
update test_table
set
name = data_table.new_name
, note = data_table.new_note
from
(
values
(1, 'one_upd', 'ichi') -- 動的に作るならここをパラメータにしてあげる
, (2, 'two_upd', 'ni') -- 動的に作るならここをパラメータにしてあげる
, (3, 'three_upd', 'san') -- 動的に作るならここをパラメータにしてあげる
) as data_table(id, new_name, new_note)
where
test_table.id = data_table.id
;
パターン3 配列をパラメータとして渡す
update test_table
set
name = data_table.new_name
, note = data_table.new_note
from
(
select
unnest (array [1,2,3]) as id -- 動的に作るならここのarray[]をパラメータにしてあげる
, unnest (array ['one_upd', 'two_upd', 'three_upd']) as new_name -- 動的に作るならここのarray[]をパラメータにしてあげる
, unnest (array ['ichi', 'ni', 'san']) as new_note -- 動的に作るならここのarray[]をパラメータにしてあげる
) as data_table
where
test_table.id = data_table.id
;
3.bulk upsert
- バージョン9.5からはon conflict句を使う事で簡単にupsertを実現できるみたい
- 今回は9.4でも動かしたかったのでひとまず割愛
- 実行前のテーブルの状態は、以下を想定
| id | name | note |
|---|---|---|
| 1 | (null) | (null) |
with
-- 更新する値
tmp(id, name, note) as (
values
(1, 'one', '壱')
, (2, 'two', '弐')
, (3, 'three', '参')
),
-- updateを実施
upsert as (
update test_table tt
set name = tmp.name, note = tmp.note
from tmp
where tt.id = tmp.id
returning tt.id
)
-- update対象が無ければinsert
insert into test_table (id, name, note)
select tmp.id, tmp.name, tmp.note
from tmp
where tmp.id not in (
select id from upsert
);
注意点
-
WITH内のデータ変更文は正確に１回のみ実行され、主問い合わせがその出力をすべて（実際にはいずれか）を呼び出したかどうかに関係なく、常に完了します。 これが、前節で説明した主問い合わせがその出力を要求した時のみにSELECTの実行が行われるというWITH内のSELECTについての規則と異なることに注意してください。
-
つまり、以下のクエリを実行した場合もwith内に記載したupdateが実行される。意図してそんなことはしないと思うけど、一応注意かな？
with
-- 更新する値
tmp(id, name, note) as (
values
(1, 'one', '壱')
, (2, 'two', '弐')
, (3, 'three', '参')
),
-- updateを実施
upsert as (
update test_table tt
set name = tmp.name, note = tmp.note
from tmp
where tt.id = tmp.id
returning tt.id
)
-- 確認用、呼び出しを行わなくてもwith内の更新文は実行される
select * from tmp;
-- 実行後を確認、値がupdateされている
select * from test_table;
実際に上記クエリを確認してみた結果：http://sqlfiddle.com/#!17/db3f3/25