sqlで複数テーブルを比較して、存在しないデータを片方のテーブルにinsert intoしたい。

Question

前提

Postgres12

実現したいこと

・2つのテーブルを比較して、片方のテーブルに存在しないデータは追加し、
存在するデータは更新するようにしたい。

　→ testテーブルに、tmp_testテーブルからデータを追加したい。

　　→ 指定の日以降のデータでtestテーブルに存在しないけどtmp_testテーブルには存在するデータを追加したい

発生している問題・エラーメッセージ

意図したレコードが追加されない

該当のソースコード

INSERT INTO test
SELECT
   id
  ,name
  ,time_stamp
FROM
  tmp_test
WHERE NOT EXISTS
  (
    select
       id
      ,name
      ,time_stamp
    from
      test
    where
      time_stamp >= cast( '2022-06-05' as timestamp)
  )
  ON CONFLICT ON CONSTRAINT test_pkey
  DO UPDATE SET
     name = excluded.name
    ,time_stamp = excluded.time_stamp
;

それぞれのテーブル内容

primary key はtestテーブル側のみ idで指定しています

[testテーブル]

 id | name |         ts
----+------+---------------------
  1 | A    | 2022-06-03 01:00:00
  2 | B    | 2022-06-04 01:00:00
  3 | C    | 2022-06-04 11:00:00
  4 | D    | 2022-06-05 20:00:00
  5 | Z    | 2022-06-03 01:00:00
  9 | F    | 2022-06-02 01:00:00

[tmp_testテーブル]

 id | name |         ts
----+------+---------------------
  2 | Y    | 2022-06-04 23:00:00
  5 | Z    | 2022-06-03 01:00:00
  6 | X    | 2022-06-05 01:00:00
  9 | F    | 2022-06-02 01:00:00

追加されてほしいデータ

上記のsql, テーブルの場合、
2022-06-05 以降のデータでtestテーブルには存在しない
id = 6, name = X, ts = 2022-06-05 01:00:00 の
データのみがtestテーブルに追加されてほしいです。

実際には上記を実行してもレコードは追加されず、
INSERT 0 0で終了してしまいます。

Answer 1

1. tmp_testテーブルからtime_stampが2022/06/05以降のデータを取得
2. 1.で取得できたデータのうち、同じidのデータがtestテーブルあればUPDATE、なければINSERT

やりたいことはこれであっていますか? あっているのであれば、以下のようなSQLで実現可能かと思います。

INSERT INTO test (id, name, time_stamp)
SELECT id, name, time_stamp
FROM tmp_test 
WHERE time_stamp >= timestamp '2022-06-05'
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, time_stamp = EXCLUDED.time_stamp;

Comments

[000mm]

回答ありがとうございます！
やりたいことは、明記していただいたことで半分合っているのですが、
「1. tmp_testテーブルからtime_stampが2022/06/05以降のデータを取得」のところで、
「1. tmp_testテーブルからtime_stampがyyyy/mm/dd以降のデータを取得、ただしtestテーブルのyyyy/mm/dd以降のデータの中にtmp_testから取得するデータが既に含まれている場合は取得しない」としたいと考えています。

例えば指定するtime_stampを2022/06/03とした場合、
tmp_testテーブルではid = 2,5,6が対象になりますが、
testテーブルの2022/06/03以降のデータの中に
id:5 のデータは既に存在しているため取得対象とはしたくなく、
testテーブルには含まれないid = 6と、
testテーブルにidは含まれているもののその他の値が異なる id = 2のみを取得対象とし、
その後
「2. 1.で取得できたデータのうち、同じidのデータがtestテーブルあればUPDATE、なければINSERT」という形で動いてほしいのですが可能でしょうか…?

[neko_the_shadow]

やりたいことが追記していただいた通りであれば、現状提示しているSQLでも想定通り稼働するかと思います。
問題となっているid:5ですが、提示しているSQLでは、tmp_testの値でUPDATEしようとします。
ただ、testテーブルのid:5の値とtmp_testテーブルのid:5の値が同じなのであれば、tmp_testの値でUPDATEしても問題ないかと思います。

[000mm]

続いてご回答いただき誠にありがとうございます…！！
ご提示いただいたsqlで対応しようと思います！

もし良ければなのですが、私自身が書いたSQLでレコードが1つも取得できない理由というのがいくら考えても思い付かず…、問題点が理解できていないままですので、もし可能であれば私のSQLはなぜ誤りなのかというのをご指摘いただけると嬉しいです…！

[neko_the_shadow]

EXISTS句のサブクエリが、tmp_testとのつながりをもっていない、必ず結果を返すクエリになってしまっているのが問題だと思われます。

[000mm]

ありがとうございます！！