INSERT支持ON CONFLICT子句

可获得性

本特性自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;