PostgreSQL的upsert功能(insert on conflict do)的用法

建表语句：

DROP TABLE IF EXISTS "goods";
CREATE TABLE "goods" (
  "store_cd" int4 NOT NULL,
  "good_cd" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default"
);

INSERT INTO "goods" VALUES (101, '1', '张三');
INSERT INTO "goods" VALUES (102, '2', '李四');
INSERT INTO "goods" VALUES (103, '3', '王五');

ALTER TABLE "goods" ADD CONSTRAINT "pr_cd_key" PRIMARY KEY ("store_cd", "good_cd");

表数据：

数据存在则更新数据，不存在则插入数据

INSERT INTO GOODS VALUES ( 104, '4', '赵六' ) 
ON CONFLICT ON CONSTRAINT pr_key_cd DO
UPDATE 
	SET NAME = '更新' 
WHERE
	GOODS.STORE_CD = '104' 
	AND GOODS.GOOD_CD = '4'

pr_key_cd为必须为唯一主键，也可以用下面写法（注意：必须保证筛选出数据唯一）

INSERT INTO GOODS VALUES ( 104, '4', '赵六' ) 
ON CONFLICT ( STORE_CD, GOOD_CD ) DO
UPDATE 
	SET NAME = '更新' 
WHERE
	GOODS.STORE_CD = '104' 
	AND GOODS.GOOD_CD = '4'

上面的两种的写法，是先执行insert如果主键冲突则执行update，没有冲突就执行insert了。要是想先执行update语句呢？

update更新失败执行insert，更新成功则执行update。

WITH TABLE1 AS ( UPDATE GOODS SET NAME = '更新' WHERE STORE_CD = '104' AND GOOD_CD = '4' RETURNING * ) 
INSERT INTO GOODS SELECT  104, '4', '赵六' 
WHERE NOT EXISTS ( SELECT 1 FROM TABLE1 WHERE STORE_CD = '104' AND GOOD_CD = '4' )