PostgreSQLでbulk insert/update/upsert

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

実際に動かしてみての細かい確認は以下に記載
[PostgreSQLでbulk insert/update/upsert - yu/logs/*](https://blog.yuuuuukou.com/entry/2021/06/16/211415)

## 0.確認用の環境

### 0.1.前提：PostgreSQLのバージョン

PostgreSQL9.6([SQL Fiddle](http://sqlfiddle.com/#!17) で確認)

### 0.2.確認用のテーブル

適当に簡単なテーブルを作成

``` sql
create table test_table (
  id   integer not null primary key
  , name varchar(100)
  , note varchar(255)
);
```

## 1.bulk insert

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


## 2.bulk update

- 実行前のテーブルの状態は、前述のbulk insert実施後を想定

### パターン1 case式で分岐させる

``` sql
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 各値をパラメータとして渡す

``` sql
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 配列をパラメータとして渡す

``` sql
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)|

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

```

### 注意点

- [7.8. WITH問い合わせ（共通テーブル式）](https://www.postgresql.jp/document/9.6/html/queries-with.html) より

    > WITH内のデータ変更文は正確に１回のみ実行され、主問い合わせがその出力をすべて（実際にはいずれか）を呼び出したかどうかに関係なく、常に完了します。 これが、前節で説明した主問い合わせがその出力を要求した時のみにSELECTの実行が行われるというWITH内のSELECTについての規則と異なることに注意してください。  

- つまり、以下のクエリを実行した場合もwith内に記載したupdateが実行される。意図してそんなことはしないと思うけど、一応注意かな？

``` sql
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

- [Faster data updates with CartoDB | CARTO Blog](https://carto.com/blog/faster-data-updates-with-cartodb/:title)

- [超倍速！？複数のレコードの更新を1回で実施するbulk update！ | Course out](https://dream-target.jp/2018/02/17/130/:title)

- https://stackoverflow.com/questions/7019831/bulk-batch-update-upsert-in-postgresql