yu/logs/*

技術メモ　など

PostgreSQLでbulk insert/update/upsert

PostgreSQL

PostgreSQLでbulk insert/update/upsertしたいときのメモ

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, update, upsertの中だと一番素直。特に言う事が無い

1.1.クエリ

insert into test_table (id, name, note)
values
(1, 'one', '壱')
, (2, 'two', '弐')
, (3, 'three', '参')
;

1.2.実行後の状態

id name note
1 one 壱
2 two 弐
3 three 参

1.3.実際にSQL Fiddleで実行した結果のリンク

http://sqlfiddle.com/#!17/69d01/2

2.bulk update

2.1.クエリ

  • 何パターンか実現する方法があるみたい、とりあえずひと通り並べてみる
  • それぞれのパフォーマンスも気になるところだけど、少なくとも単発のクエリを大量に投げるよりは早そうなので今回は割愛
  • 実行前のテーブルの状態は、前述のbulk insert実施後を想定

2.1.1.パターン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.1.2.パターン2 各値をパラメータとして渡す

  • 各値をパラメータとして渡して、それをテーブルとみなしてupdate select するイメージ？
  • 動的にクエリを作ることを考えた時、プログラム側でList型のデータ構造で持っているならパターン1～3の中で一番簡単に作れそう？
  • プレースホルダの個数が多くなってしまいそう？
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
;

2.1.3.パターン3 配列をパラメータとして渡す

  • 配列でパラメータとして渡して、それをテーブルとみなしてupdate select するイメージ？
  • 動的にクエリを作ることを考えた時、カラムごとに配列で渡すので、プログラム側でList型のデータ構造で持ってたりするとちょっと作りづらそう？
  • 配列で渡すので、プレースホルダの個数を削減できそう？
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
;

2.2.実行後の状態

id name note
1 one_upd ichi
2 two_upd ni
3 three_upd san

2.3.実際にSQL Fiddleで実行した結果のリンク

3.bulk upsert

  • バージョン9.5からはon conflict句を使う事で簡単にupsertを実現できるみたい
    • 今回は9.4でも動かしたかったのでひとまず割愛
  • 実行前のテーブルの状態は、以下を想定
id name note
1 (null) (null)

3.1.クエリ

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
);

3.2.実行後の状態

id name note
1 one 壱
2 two 弐
3 three 参

3.3.実際にSQL Fiddleで実行した結果のリンク

http://sqlfiddle.com/#!17/db3f3/3

3.4.補足

3.4.1.補足1 どう動いてるのか

  • with内にデータ変更文を記載することで、同じ問い合わせ内で複数の異なる操作を行うことが出来る

  • returningは更新操作で値を返すPostgreSQLの拡張

    • 上記であれば、select id from upsertの結果として以下が返ってきている
    id
    1

  • select文だけで実行した例

  • http://sqlfiddle.com/#!17/db3f3/23
  • 今回実行した例(http://sqlfiddle.com/#!17/db3f3/3)ではupdate実施したレコードのidが返ってくるので、これをnot inに指定してあげる事で、updateを行った対象以外をinsertすることができる

3.4.2.補足2 注意点？

  • 7.8. WITH問い合わせ（共通テーブル式） より

    • 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

4.参考URL