INSERT INTO ON CONFLICT DO UPDATE

功能描述

INSERT语法中ON CONFLICT DO UPDATE子句根据表索引的唯一性，决定当插入的数据存在唯一性冲突时的动作：无冲突时可直接INSERT，若存在冲突时，则根据此子句的内容执行UPDATE。

Vastbase在PostgreSQL兼容模式下支持 ON CONFLICT DO UPDATE 子句的如下用法：

支持使用 RETURNING 返回实际插入的字段值，或是冲突更新后的字段值。
支持指定冲突后的动作时使用 WHERE 子句。即在INSERT语句遇到冲突后，满足WHERE条件时数据会被更新，不满足条件时不执行。

注意事项

上述功能仅在数据库兼容模式为PostgreSQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='PG'）。
使用ON CONFLICT DO UPDATE时，用户必须要有该表的SELECT、UPDATE权限，唯一约束（主键或唯一索引）的SELECT权限。
RETURNING列表的语法与SELECT的输出列表一致。

语法格式

INSERT INTO table_name ...
ON CONFLICT [ conflict_target ] conflict_action
[ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

其中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子句用于指定返回实际插入的字段值，或是冲突更新后的字段值。
- RETURNING *代表可以返回被插入或更新行的所有字段。
- output_expression指定返回列的名称，该表达式可以使用table_name指定的表中的任何列。
- [ AS ] output_name为RETURNING返回的字段指定别名。
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的条件。在INSERT语句遇到冲突后，满足WHERE条件时数据会被更新，不满足条件时不执行。

示例

示例1： ON CONFLICT DO UPDATE使用RETURNING子句。

1、创建测试表，创建表上的唯一索引(id_1,id_3)。

CREATE TABLE tb2_1161471
(
id_1 int1,
id_2 int2,
id_3 int4
);
create unique index on tb2_1161471(id_1,id_3);

2、执行第一次插入，插入或更新的判断条件为该条数据是否违反了唯一约束(id_1,id_3)，此处未发生冲突所以插入一条数据，并使用RETURNING子句返回id_1字段。

insert into tb2_1161471 values(1,2,3) on conflict(id_1,id_3) do update set id_1 =0,id_3 =0 returning id_1;

返回结果如下：

 id_1
------
 1
(1 row)

3、查看测试表当前数据。

select * from tb2_1161471;

返回结果如下：

 id_1 | id_2 | id_3
------+------+------
 1    |    2 |    3
(1 row)

4、执行第二次插入，插入或更新的判断条件为该条数据是否违反了唯一约束(id_1,id_3)，此处(1,3)与表中数据发生冲突，故执行更新操作，将(id_1,id_3)数据更新为(0,0)，并使用RETURNING子句返回更新行的所有字段。

insert into tb2_1161471 values(1,2,3) on conflict(id_1,id_3) do update set id_1 =0,id_3 =0 returning *;

返回结果如下：

 id_1 | id_2 | id_3
------+------+------
 0    |    2 |    0
(1 row)

5、查看测试表数据。

select * from tb2_1161471;

返回结果如下，表仅有一条数据，且id_1和id_3都被更新：

 id_1 | id_2 | id_3
------+------+------
 0    |    2 |    0
(1 row)

6、执行第三次插入，插入或更新的判断条件为该条数据是否违反了唯一约束(id_1,id_3)，此处(1,3)与表中的数据无冲突，故直接插入，并使用RETURNING子句返回插入行的所有字段。

insert into tb2_1161471 values(1,2,3) on conflict(id_1,id_3) do update set id_1 =0,id_3 =0 returning *;

返回结果如下：

 id_1 | id_2 | id_3
------+------+------
 1    |    2 |    3
(1 row)

7、查看测试表数据。

select * from tb2_1161471;

返回结果如下：

 id_1 | id_2 | id_3
------+------+------
 0    |    2 |    0
 1    |    2 |    3
(2 rows)

8、执行第四次插入，插入或更新的判断条件为该条数据是否违反了唯一约束(id_1,id_3)，此处(1,3)与表中数据发生冲突，更新后的数据(0,0)仍违反唯一约束，因此如下语句执行将报错。

insert into tb2_1161471 values(1,2,3) on conflict(id_1,id_3) do update set id_1 =0,id_3 =0 returning *;

报错信息如下：

ERROR:  duplicate key value violates unique constraint "tb2_1161471_id_1_id_3_idx"
DETAIL:  Key (id_1, id_3)=(0, 0) already exists.

示例2： conflict_action中使用WHERE子句。

1、创建测试表（指定id字段为主键），插入测试数据。

create table user_admin_t (id varchar primary key,name varchar,password varchar);
insert into user_admin_t values('1','LiHua','asdfghjkl'),('2','LiLy','Admin@123'),('3','Tom','Aa@#09M');

2、查看测试表数据。

select * from user_admin_t;

返回结果如下：

 id | name  | password
----+-------+-----------
 1  | LiHua | asdfghjkl
 2  | LiLy  | Admin@123
 3  | Tom   | Aa@#09M
(3 rows)

3、执行第一次插入：若不存在id为1的记录则插入这条数据，若存在，则判断其password长度是否小于6，小于6时为其更新password，否则不采取任何措施。

select * from user_admin_t;
INSERT INTO user_admin_t (id,password) VALUES ('1','newpassword') ON CONFLICT(id) DO UPDATE set password = 'newPassword' where length(user_admin_t.password)<6;

id为1的用户密码长度不小于6，未插入或更新任何数据。

INSERT 0 0

4、执行第二次插入：若不存在id为1的记录则插入这条数据，若存在，则判断其name是否为LiHua，条件符合时更新password，否则不采取任何措施。

INSERT INTO user_admin_t (id,password) VALUES ('1','newpassword') ON CONFLICT (id) DO UPDATE set password = 'newPassword' where user_admin_t.name='LiHua';

5、查看测试表数据。

select * from user_admin_t;

返回结果如下，id为1且name为LiHua的记录中，password字段已被更新：

 id | name  |  password
----+-------+-------------
 2  | LiLy  | Admin@123
 3  | Tom   | Aa@#09M
 1  | LiHua | newPassword
(3 rows)

VastbaseG100

INSERT INTO ON CONFLICT DO UPDATE

功能描述

注意事项

语法格式

参数说明

示例

弹框头部