insert into conflict

现有一个业务需求：需要根据关联关系，用B表的数据修改A表中的一个字段。

当看到这个需求的时候，我首先想到的是常用的DB中那种UPDATE...JOIN...的语法。

但是经过测试，发现Hologres不支持MySQL那种UPDATE...JOIN...的语法，所以只能去查询文档，发现Hologres支持INSERT ON CONFLICT语法，可以实现类似的功能。

INSERT ON CONFLICT语句用于在指定列插入某行数据时，如果主键存在重复的行数据，则对该数据执行更新或跳过操作。 ———————————————— 版权声明：本文为CSDN博主「胜利的曙光」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/shenglishuguang/article/details/123741042

使用限制

  • INSERT ON CONFLICT语句的条件必须包含所有主键。
  • 如果系统提示实例版本过低不支持该功能。您可以执行如下命令或提交工单升级实例至最新版本。
set hg_experimental_enable_insert_on_conflict = on;

注： xtp upsert必须依赖主键，insert on conflict可以是主键或者唯一索引

应用场景

INSERT ON CONFLICT命令适用于通过SQL方式导入数据的场景。

命令格式 INSERT ON CONFLICT的语法格式如下。

INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] { VALUES ( { expression } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ]

where conflict_target is pk

ON CONSTRAINT constraint_name

and conflict_action is one of:

DO NOTHING
DO UPDATE SET { column_name = { expression } |
                ( column_name [, ...] ) = ( { expression } [, ...] ) |
              } [, ...]
          [ WHERE condition ]

参数说明如下表所示。

参数

描述

DO NOTHING

在指定列插入某行数据时，主键存在重复的行数据，则对该数据执行跳过操作。

DO UPDATE

在指定列插入某行数据时，主键存在重复的行数据，则对该数据执行更新操作。

expression

对应列执行的相关表达式。

使用示例

drop table conflict_1;
drop table conflict_2;
create table conflict_1(
  a int not null primary key, 
  b int ,
  c int);
insert into conflict_1 values(1,1,1);
insert into conflict_1 values(2,3,4);

create table conflict_2(
  a int not null primary key, 
  b int ,
  c int);
insert into conflict_2 values(1,5,6);
insert into conflict_2 values(3,5,6);
select * from conflict_1;
select * from conflict_2;

--主键相同时，将表conflict_2的某列数据更新到表conflict_1中。
insert into conflict_1(a,b) select a,b from conflict_2 on conflict(a) do update set b = excluded.b; 
--merge into 方式实现，xtp不支持别名：
--1
MERGE INTO conflict_1 x USING conflict_2 y   ON (x.a=y.a)
WHEN MATCHED THEN UPDATE 
SET b=y.b
WHEN NOT MATCHED THEN
INSERT(a,b,c) VALUES(y.a,y.b,y.c); 
--2
MERGE INTO conflict_1 x USING (select k.a,k.b,k.c from conflict_2 k) y ON (x.a=y.a)
WHEN MATCHED THEN
UPDATE SET b=y.b
WHEN NOT MATCHED THEN
INSERT(a,b,c) VALUES(y.a,y.b,y.c); 
-- 需要特别注意的是：上面SQL语句中的set b = excluded.b中，excluded是固定写法，不能改变，set 后面的字段名和excluded.后面的字段名也必须一致，不然会报错“org.postgresql.util.PSQLException: ERROR: column excluded.字段名 does not exist”

--主键相同时，将表conflict_2的某一行数据全部插入至表conflict_1中。
insert into conflict_1 values(2,7,8) on conflict(a) do update set b = excluded.b, c = excluded.c where conflict_1.c = 4; 

--主键相同时，向表conflict_1插入表conflict_2的数据，系统直接跳过表conflict_2的数据（即插入数据失败）。
insert into conflict_1 select * from conflict_2 on conflict(a) do nothing; 

--do nothing不指定冲突列时，默认冲突列为主键。
insert into conflict_1 select * from conflict_2 on conflict do nothing; 

--指定主键constrain的名称。
insert into conflict_1 select * from conflict_2 on conflict on constraint conflict_1_pkey do update set a = excluded.a;

---更新整行数据。
insert into tmp1_on_conflict values(1,2,3) on conflict(a) do update set (a, b ,c )= ROW(excluded.*);

merge into

merge into的形式：

  1. MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
  2. WHEN MATCHED THEN
  3. [UPDATE sql]
  4. WHEN NOT MATCHED THEN
  5. [INSERT sql]

drop table conflict_1;
drop table conflict_2;
create table conflict_1(
  a int not null primary key, 
  b int ,
  c int);
insert into conflict_1 values(1,1,1);
insert into conflict_1 values(2,3,4);

create table conflict_2(
  a int not null primary key, 
  b int ,
  c int);
insert into conflict_2 values(1,5,6);
insert into conflict_2 values(3,5,6);
insert into conflict_2 values(10,11,12);
select * from conflict_1;
select * from conflict_2;
--主键相同时，将表conflict_2的某列数据更新到表conflict_1中。
--1
MERGE INTO conflict_1 x USING conflict_2 y   ON (x.a=y.a)
WHEN MATCHED THEN UPDATE 
SET b=y.b
WHEN NOT MATCHED THEN
INSERT(a,b,c) VALUES(y.a,y.b,y.c); 
--2
MERGE INTO conflict_1 x USING (select k.a,k.b,k.c from conflict_2 k) y ON (x.a=y.a)
WHEN MATCHED THEN
UPDATE SET b=y.b
WHEN NOT MATCHED THEN
INSERT(a,b,c) VALUES(y.a,y.b,y.c); 
--只做insert
MERGE INTO conflict_1 x USING conflict_2 y ON (x.a=y.a) 
WHEN NOT MATCHED THEN 
INSERT(a,b,c) VALUES(y.a,y.b,y.c);
--只做insert，并加where条件
MERGE INTO conflict_1 x USING conflict_2 y ON (x.a=y.a) 
WHEN NOT MATCHED THEN 
INSERT(a,b,c) VALUES(y.a,y.b,y.c) where y.c=12;
--只做update
MERGE INTO conflict_1 x USING conflict_2 y   ON (x.a=y.a)
WHEN MATCHED THEN UPDATE 
SET b=y.b;