我有一个postgres SQL查询，我想在这里做upsert。我的pk1, pk2列是复合主键。dk1, dk2, timek如果与主键约束冲突，我想更新3列。同样，当我更新冲突列时，例如timek列，那么我需要检查新值是否小于旧值，并相应地选择是否需要使用旧值或新值。这是我的查询，但是会引发错误。我是Postgres SQL的新手，请提示出什么问题了？
INSERT INTO data_reports (pk1, pk2, dk1, dk2, dk3, timek, valuek) VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT (pk1, pk2) DO UPDATE SET
dk1 = EXCLUDED.dk1,
dk2 = CASE WHEN (dk2 < EXCLUDED.dk2) THEN EXCLUDED.dk2 ELSE dk2 END), // I believe dk2 (existing value in DB) is not selected but how to do that
timek = CASE WHEN (timek < EXCLUDED.timek) THEN EXCLUDED.timek ELSE timek END);
data_reports.dk2。(s和7 )s。--，或以/*和括起评论*/。INSERT INTO data_reports (pk1,pk2,dk1,dk2,dk3,timek,valuek)
VALUES (1,1,9,9,9,9,9)
ON CONFLICT (pk1,pk2) DO
UPDATE
SET
dk1 = EXCLUDED.dk1,
dk2 =
(CASE
WHEN (data_reports.dk2 < EXCLUDED.dk2) THEN EXCLUDED.dk2
ELSE data_reports.dk2
END),
timek =
(CASE
WHEN (data_reports.timek < EXCLUDED.timek) THEN EXCLUDED.timek
ELSE data_reports.timek
END);
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句