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

桃扇骨 2024-04-19 09:45 52阅读 0赞

建表语句：

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");

表数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODg4MzMzOA_size_16_color_FFFFFF_t_70][]

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

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' )

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODg4MzMzOA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7b3e3eb1f13f4eaaa371743d8e4e42d9.png