可获得性
本特性自MogDB 5.0.6版本开始引入。
特性简介
MogDB支持INSERT ON CONFLICT DO UPDATE/DO NOTHING语法及其功能，ON CONFLICT子句指定引发唯一约束冲突时，执行ON CONFLICT后面的语句，将INSERT行为变更为UPDATE或DO NOTHING以避免报错。
客户价值
兼容PostgreSQL 9.5新增语法，减少应用程序的迁移代价。
语法说明
INSERT新增ON CONFLICT子句：
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
[ ON CONFLICT [conflict_target] DO { NOTHING | { UPDATE SET column_name = { expression | DEFAULT } } [, ...] [ WHERE condition ] } ]
其中conflict_target可以是以下之一：
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name
可选的ON CONFLICT子句提供了一种处理插入冲突的方法，主要用于解决唯一约束或者主键约束导致的插入失败问题。当尝试插入一行数据时，如果唯一约束或主键约束已经存在相同值的数据，ON CONFLICT子句可以指定在发生冲突时的行为，例如执行更新操作而不是插入新数据，或者忽略冲突而不进行任何操作。
-
conflict_target
通过选择索引，指定ON CONFLICT对哪些冲突采取替代操作。要么执行唯一索引推断，要么显式命名一个约束。对于
ON CONFLICT DO NOTHING来说，conflict_target是可选的。在被省略时，与所有有效约束（以及唯一索引）的冲突都会被处理。对于ON CONFLICT DO UPDATE，必须提供conflict_target。 -
index_column_name
索引列名。
-
index_expression
与
index_column_name类似，但用于索引中出现的列（而不是简单列）的表达式。 -
collation
当指定时，要求相应的
index_column_name或index_expression使用特定的排序规则(collation)才能匹配。通常情况下会被省略，因为排序规则通常不会影响是否违反约束。 -
opclass
当指定时，要求相应的
index_column_name或index_expression使用特定的运算符类(operator class)才能匹配。通常情况下会被省略。 -
index_predicate
用于允许推断部分唯一索引。任何满足该谓词（不一定需要是部分索引）的索引都能被推断。
-
constraint_name
用名称显式指定一个仲裁者约束， 而不是推断约束或者索引。
-
condition
返回布尔类型值的表达式，只有该表达式返回true的记录才会被更新。
特性约束
- 目标表不支持外部表
- 目标表不支持视图，与PostgreSQL行为不一致
- INSERT中存在查询语句时不支持SQL Bypass
- 不支持列存表
- 适用于A和PG兼容模式
- 对PG有兼容性要求的场景需要关闭allow_concurrent_tuple_update参数
- 对PG有兼容性要求的场景需要设置lockwait_timeout = 0
示例
drop index if exists i_upsert;
drop table if exists t_upsert cascade;
-- 创建普通表并插入数据
CREATE TABLE t_upsert(
id int ,
name text,
price numeric
);
insert into t_upsert select generate_series,'test' || generate_series,generate_series*10 from generate_series(1,10);
select * from t_upsert order by 1 limit 10;
--创建索引
create unique index i_upsert on t_upsert(id);
select a.relname,b.indnatts,b.indisusable,b.indisunique,b.indisprimary
from pg_class a,pg_index b
where a.oid = b.indexrelid and b.indrelid = (select oid from pg_class where relname = 't_upsert') order by 1;
--插入重复值，insert on conflict语句中的conflict_target是索引列名
insert into t_upsert values(3,'gram',5.5) on conflict(id) do update set name='gram';
select * from t_upsert order by 1;
--清除数据
drop index if exists i_upsert;
drop table if exists t_upsert cascade;
