あればUpdateなければInsert同じデータなら何もしないをsql一本で書きたい

Question

前提・実現したいこと

前提
postgreSQL
言語php
フレームワーク　Laravel

DBのデータ

id=1
name=ぶどう
last_update_time=2019/01/01 01:01:01

id=2
name=ぶどう
last_update_time=2019/01/01 01:01:01

id=3
name=ぶどう
last_update_time=2019/01/01 01:01:01

更新用データ

id=1
name=ぶどう
last_update_time=2019/01/01 01:01:01

id=2
name=バナナ
last_update_time=2019/01/31 10:01:01

id=3
name=みかん
last_update_time=2019/01/31 10:01:01

id=4
name=なし
last_update_time=2019/01/31 10:01:01

■実現したいこと
・更新用のデータでDBの値をUpdate/Insertしたい
・last_update_timeが同じデータは更新処理もしたくない
・上記の例で言えば更新用データ→DBデータに対する挙動は
id=1 何もしない
id=2 update
id=3 update
id=4 insert

■聞きたいこと
実現する為にSQL一本で可能かどうか

■試したこと・考え
INSERTとUPDATEをCONFLICT句で
と思ったんですが、
同じデータの場合はたとえデータの変更がなくてもUpdate句が走ってしまいます。
よって更新用データでループして、last_update_timeがDBの値と異なればUpdateデータがなければInsert
という処理をループする、しか思いつきませんでした。
しかし処理件数は1000 件を超えることもあり、かつ数分に一度走る監視用のような処理のため
できればループはしたくありません。(殆どが変更のないデータである可能性が高いため）

SQL1本で上記の処理は実現可能でしょうか？
また、一本は無理でも、上記処理より良い処理はあるでしょうか

Comments

[退会済みユーザー]

「あればInsertなければUpdate」←逆じゃない？

[ms5025]

逆ですね。すいません。修正します

[sazi]

（削除)

[sazi]

(削除)

[sazi]

(削除)

[退会済みユーザー]

Laravelを使っているなら、updateOrCreate() は試したのでしょうか？

Answer 1

with sel as (select * from (values
                      (1, 'ぶどう', '2019/01/01 01:01:01'::date),
                      (2, 'バナナ', '2019/01/31 10:01:01'::date),
                      (3, 'みかん', '2019/01/31 10:01:01'::date),
                      (4, 'なし', '2019/01/31 10:01:01'::date)
                     ) as w(id, name, last_update_time)),
with upd as (update table set name=sel.name
                              last_update_time=sel.last_update_time
                          from sel
                          where table.id = sel.id
                            and table.last_update_time <> sel.last_update_time
                          returning sel.id)
insert into table select * from sel
    where not exists (select id from upd)
on conflict on constraint pkey
do nothing

動作としては、selで更新データを作成して、updでidが同じでlast_update_timeが異なるものを更新しています。（2,3が対象）
更新対象がなければ、insertになりますが、constraintで弾いてます。（4はOK）
idにprimary keyがあると想定していますので、1は弾かれ、何も処理はしません。

性能評価などはしていないため、コストかかって運用にあっていなければスミマセン。

Comments

[ms5025]

ありがとうございます。
参考になりました。
こちらの方法で実現できました。
ありがとうございます。

Answer 2

実現する為にSQL一本で可能かどうか

with式とreturningを活用すれば可能です。

下記は、対象のテーブルをtest、データをワーク(一時テーブルなど）に格納した更新用データをtest2として記述しています。

    with upd AS (
      UPDATE test tgt SET 
        name=val.name
      , last_update_time=val.last_update_time
      FROM test2 val 
      WHERE tgt.id=val.id 
       and (tgt.name!=val.name or tgt.last_update_time!=val.last_update_time)
      returning tgt.*
    )
    INSERT INTO test tgt SELECT * FROM test2 src
    WHERE id NOT IN (SELECT id FROM upd)
      and not exists(select 1 from test where id=src.id)

ですが、レスポンスを考えるなら、updateとinsertをそれぞれで行った方が高速です。

UPDATE test tgt SET 
  name=val.name
, last_update_time=val.last_update_time
FROM test2 val 
WHERE tgt.id=val.id 
 and (tgt.name!=val.name or tgt.last_update_time!=val.last_update_time)
;
INSERT INTO test tgt SELECT * FROM test2 src
WHERE not exists(select 1 from test where id=src.id)
;

高速にするポイントは、更新対象データをテーブルデータにする事です。
一時テーブルを利用すれば排他も気にする必要はありません。

create temp table test2(id int, name text, last_update_time timestamp) on commit drop;
insert into test2 values
  (1, 'ぶどう', '2019/01/01 01:01:01')
 ,(2, 'バナナ', '2019/01/31 10:01:01')
 ,(3, 'ミカン', '2019/01/31 10:01:01')
 ,(4, 'なし'  , '2019/01/31 10:01:01')
;
create index test_index on test2(id, name, last_update_time);

Comments

[sazi]

ワークテーブルでは無く、with式に更新用データを展開する方がお手軽かもしれません。
但し、動的SQLにする必要がありますが。

Answer 3

単純にUPDATEにWHEREで条件を追加してみてはダメでしょうか？
PosgreSQL10 ですが、実行したところWHERE区の条件に一致しない場合は更新されませんでした。
（update_atは更新が走ったかどうか確認する為のカラムなので無視してください）

INSERT INTO fruits (id, name, last_update_time, update_at) VALUES (1,'ぶどう','2019/01/01 01:01:01',now())
ON CONFLICT ON CONSTRAINT fruits_pkey
DO UPDATE SET name='ぶどう',last_update_time='2019/01/01 01:01:01',update_at=now()
WHERE fruits.id=1 and fruits.last_update_time < '2019/01/01 01:01:01';

WHEREのカラムにはテーブル名を明記しないとSQL実行時に曖昧なカラム参照エラーになったので付けています。

Comments

[storm3]

>同じデータの場合はたとえデータの変更がなくてもUpdate句が走ってしまいます。
失礼しました。私の回答は上記の内容を汲んでいませんでした。
空振りであっても更新のSQL自体は走ってしまいますね。

Answer 4

UPSERT by テーブルがやりたくて書いてみました。
シェルでvalue区を作成する様に考えました1000件は多いですど。

CREATE TEMP TABLE hoge (id int, name varchar, last_update_time timestamp,
CONSTRAINT upst_pkey PRIMARY KEY(id));
INSERT INTO hoge (id, name, last_update_time)
values(1, 'ぶどう', '2019/01/01 01:01:01'),
      (2, 'ぶどう', '2019/01/01 01:01:01'),
      (3, 'ぶどう', '2019/01/01 01:01:01');


--UPSERT by TABLE 
WITH hoge2 as (SELECT * FROM
(values 
        (1,'ぶどう','2019/01/01 01:01:01'::timestamp),
        (2,'バナナ','2019/01/31 10:01:01'::timestamp),
        (3,'みかん','2019/01/31 10:01:01'::timestamp),
        (4,'なし',  '2019/01/31 10:01:01'::timestamp)
) t1
WHERE NOT EXISTS (SELECT 1 FROM hoge WHERE id=column1 AND name=column2 AND last_update_time=column3))
INSERT INTO hoge (id, name, last_update_time)
SELECT * from hoge2
ON CONFLICT ON CONSTRAINT upst_pkey
DO UPDATE set name=excluded.name, last_update_time=excluded.last_update_time;