WITH w1 AS (SELECT ('x'||substr(md5('1'),1,16))::BIT(64)::bigint AS tra_id),
upsert AS ( UPDATE "ST_PPTN_R_copy1" SET "DRP" = 0.1,
"INTV" = 0.2, "PDR" = 0.3,"DYP" = 1.2,"WTH" = '6'
WHERE "STCD"='60810430' AND "TM"='2021-10-11 10:55:39.017' returning * )
INSERT INTO
"ST_PPTN_R_copy1" ("DRP","INTV","PDR","DYP","WTH")
SELECT 0.1, 0.2, 0.3, 1.2, '6'
FROM w1
WHERE pg_try_advisory_xact_lock(tra_id)
AND NOT exists (SELECT 1 FROM upsert
WHERE "STCD"='60810430' AND "TM" = '2021-10-11 10:55:39.017');
PostgreSQL 9.5 以下实现UPSERT
于 2021-06-23 18:35:15 首次发布