前言
Oracle迁移PG过程中遇到Merge语法的处理。PG 11开始支持Merge语法,但项目使用PG 10,所以要对代码中的Merge语句进行重写。
什么是Merge?
Oracle数据库MERGE语句_周末未至-优快云博客_merge oracle
PG实现Merge的最佳实践
项目中Merge的情况比较简单,最后我采用了INSERT...ON CONFLICT...重写
如Oracle的Merge语句
MERGE INTO PRODUCT prd
USING PRODUCT_DELTA src
ON (prd.product_name = src.product_name
AND prd.product_type = src.product_type
)
WHEN MATCHED THEN
UPDATE SET
prd.unit_price = src.unit_price,
modified_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT(product_name, product_type, unit_price, modified_date)
VALUES(src.product_name, src.product_type, src.unit_price, SYSDATE);
重写成Insert...on conflict...
INSERT INTO PRODUCT(product_name, product_type, unit_price, modified_date)
SELECT src.product_name,
src.product_type,
src.unit_price,
now() modified_date
FROM ( SELECT
product_name,
product_type,
unit_price
FROM PRODUCT_DELTA
) src
ON CONFLICT(product_name, product_type)
DO UPDATE
SET
unit_price = excluded.unit_price,
modified_date = excluded.modified_date;