UPSERT功能

功能描述

UPSERT用于向目标表中插入或更新数据，该子句中指定了INSERT时的冲突键，以及冲突后的处理方式：当插入值违背了唯一约束时，则更新数据，不冲突则直接插入数据。

因此被称为UPSERT功能——“UPDATE 或 INSERT”。在没有无关错误的前提下，此功能保证了一个INSERT或者UPDATE结果的原子性。

注意事项

支持insert的数据类型允许在SQL的insert部分写入。
支持update的数据类型允许在upsert_clause中的update子句中更新。
conflict_action中包含where子句和returning子句的用法仅在PostgreSQL兼容模式下支持，详见INSERT INTO ON CONFLICT DO UPDATE。
upsert子句暂不支持以下场景：
- upsert子句中包含with_query子句。
- update set子句中包含子查询。
- upsert子句的conflict_target为空。
- upsert子句的conflict_target为ON CONSTRAINT name。
- upsert子句的conflict_target中包含where子句。
- upsert子句的conflict_target指定了ASC/DESC 、NULLS FIRST/LAST关键字。
- upsert在分区表、视图、外部表中使用。

语法格式

INSERT语法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT [/*+ plan_hint */] [IGNORE] INTO table_name [ @dblink_name ] [partition_clause] [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES | VALUE {( { expression | DEFAULT } [, ...] ) }[, ...] | query }
    [ upsert_clause ]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];
INSERT [ FIRST | ALL ]
[ WHEN { condition } THEN INTO table [ [ AS ] alias ] [ ( column_name [, ...] ) ] VALUES (v1, ...), ...]
{ subquery }

Vastbase支持两种风格的upsert_clause语法：

ON DUPLICATE KEY UPDATE { NOTHING | { column_name = { expression | DEFAULT } } [, ...] }
ON CONFLICT [ conflict_target ] conflict_action

冲突检测子句conflict_target可以是：

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name

冲突处理子句conflict_action可以是：

DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
            ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
            ( column_name [, ...] ) = ( sub-SELECT )
          } [, ...]
      [ WHERE condition ]

参数说明

table_name

需要执行UPSERT语句的表名。
RETURNING { * | {output_expression [ [ AS ] output_name ] }

RETURNING子句用于指定返回实际插入的字段值，或是冲突更新后的字段值。

此功能仅在数据库兼容模式为PostgreSQL时支持（即数据库初始化时指定DBCOMPATIBILITY='PG'）。
- RETURNING *代表可以返回被插入或更新行的所有字段。
- output_expression指定返回列的名称，该表达式可以使用table_name指定的表中的任何列。
- [ AS ] output_name为RETURNING返回的字段指定别名。

INSERT主语法的其余参数说明请参考《开发者指南》中的SQL语法INSERT。

ON DUPLICATE KEY UPDATE

NOTHING

ON DUPLICATE KEY UPDATE表示检测到冲突时什么也不做，忽略此条插入。
column_name = { expression | DEFAULT }

此表达式指定了冲突发生后的UPDATE动作。
- expression
  
  表达式expression支持使用子查询表达式，其语法与功能同UPDATE。子查询表达式中支持使用EXCLUDED.来选择源数据相应的列。
- DEFAULT
  
  相应的列将填充其默认值。标识列将由关联序列生成的新值填充。
  
  对于生成的列，允许指定该值，但仅指定根据其生成表达式计算该列的正常行为。

ON CONFLICT DO UPDATE

conflict_target

通过选择判断目标索引来指定哪些行与ON CONFLICT在其上采取可替代动作的行相冲突。要么执行唯一索引推断，要么显式命名一个约束。

除非指定了ON CONFLICT DO NOTHING，否则必须提供conflict_target。
( { index_column_name | ( index_expression ) } [, …] ) [ WHERE index_predicate ]

conflict_target可以执行唯一索引推断。在执行推断时，它由一个或者多个 index_column_name 列或者 index_expression 表达式以及一个可选的 index_predicate构成。所有刚好包含 conflict_target指定的列/表达式的table_name唯一索引（不管顺序）都会被推断为（选择为）仲裁者索引。如果指定了 index_predicate，它必须满足仲裁者索引（也是推断过程的一个进一步的要求）。注意这意味着如果有一个满足其他条件的非部分唯一索引（没有谓词的唯一索引）可用，它将被推断为仲裁者（并且会被ON CONFLICT使用）。如果推断尝试不成功，则会抛出一个错误。
- index_column_name
  
  table_name中一个列的名称。它被用来检测对象是否冲突。
- index_expression
  
  和index_column_name类似，但是被用来判断出现在索引定义中的table_name列（非简单列）上的表达式。
- Collation
  
  强制相应的index_column_name或index_expression使用一种特定的排序规则以便在检测冲突期间能被匹配上。默认为缺省值。排序规则不影响冲突检测，一般不指定。
- opclass
  
  强制相应的index_column_name或index_expression使用特定的操作符类以便在检测冲突期间能被匹配上。通常会被省略。
- index_predicate
  
  用于允许判断部分唯一索引。任何满足该谓词（不一定需要真的是部分索引）的索引都能被推断。
constraint_name

用名称显式地指定一个判断目标对象约束。本版本中暂不支持。
conflict_action

conflict_action指定一个可替换的ON CONFLICT动作。它可以是DO NOTHING，也可以是一个指定在冲突情况下要被执行的UPDATE动作细节的DO UPDATE子句。ON CONFLICT DO UPDATE中的SET和WHERE子句能够使用该表的名称（或者别名）访问现有的行，并且可以用特殊的excluded表访问要插入的行。注意，不支持引用系统列例如ctid作为更新值，当前版本暂时不支持sub-SELECT做更新。
- DO NOTHING
  
  检测到冲突时什么也不做。
- DO UPDATE SET
  
  检测到冲突时执行SET子句指定的动作。
  
  在SET子句中，可用EXCLUDED.来引用要插入的列值，但不支持引用系统列（比如ctid)。例如: UPDATE SET c1=EXCLUDE.c1表示将c1更新为SQL中insert部分计划插入的c1字段的值；不支持UPDATE setc1=EXCLUDE.ctid:text的用法。
{ column_name = { expression | DEFAULT }
- column_name
  
  table_name表中的一个列的名称。如有必要，列名可以用一个子域名或者数组下标限定（指向一个组合列的某些列中插入会让其他域为空）。
- expression
  
  要赋予给相应列的表达式或者值。
- DEFAULT
  
  相应的列将填充其默认值。标识列将由关联序列生成的新值填充。对于生成的列，允许指定该值，但仅指定根据其生成表达式计算该列的正常行为。
[ WHERE condition ]

WHERE条件表达式，可选。

用于指定UPDATE的条件，当冲突发生时仅更新符合WHERE条件的字段。

此功能仅在数据库兼容模式为PostgreSQL时支持（即数据库初始化时指定DBCOMPATIBILITY='PG'）。

示例

ON DUPLICATE KEY UPDATE

1、创建测试表并插入数据。

CREATE TABLE test01 (col1 INT PRIMARY KEY, col2 INT);
insert into test01 values(1,1);

2、根据col1字段值判断数据库中是否有记录'1'，有则更新字段col2为'2'，没有则插入col1为'1'。

INSERT INTO test01(col1) VALUES(1) ON DUPLICATE KEY UPDATE col2 = 2;

3、验证上一步的操作结果。

SELECT * FROM test01;

当结果显示为如下信息，表示col2的数据被更新功。

 col1 | col2 
------+------
    1 |    2
(1 row)

ON CONFLICT DO UPDATE

1、创建测试表，在字段c1上创建唯一索引。

create table tu1(c1 int unique, c2 int);

2、执行如下语句，若c1值不冲突，则插入一条数据(3,3)；若c1值冲突，则将c2更新为SQL中insert部分计划插入的c1字段的值。

insert into tu1 values(3,3) on conflict(c1) do update set c2=EXCLUDED.c2;

3、插入成功，查询测试表数据。

select * from tu1;

返回结果如下：

 c1 | c2 
----+----
  3 |  3
(1 row)

4、执行如下语句，c1值发生冲突，将c2更新为SQL中insert部分计划插入的c2字段的值。

insert into tu1 values(3,4) on conflict(c1) do update set c2=EXCLUDED.c2;

5、查询测试表数据。

select * from tu1;

冲突更新成功，返回结果如下：

 c1 | c2 
----+----
  3 |  4
(1 row)

6、执行如下语句，如c1值冲突，将什么都不做。

insert into tu1 values(3,5) on conflict(c1) do nothing;

7、查看测试表数据。

select * from tu1;

返回结果如下，表示没有执行任何操作：

 c1 | c2 
----+----
  3 |  4
(1 row)

VastbaseG100