向表中插入数据

UPSERT功能

功能描述

UPSERT功能用于往目标表中插入或更新数据。当插入数据因为主键或约束产生冲突时，更新数据，不冲突则插入数据。通过PG风格的upsert语句，指定insert时的冲突键，以及冲突后的处理方式。执行INSERT语句时，支持通过ONCONFLICT conflict_target DO conflict_action语法，指定insert时的冲突检测目标及冲突后的行为。

语法格式

INSERT完整语法

[WITH [RECURSIVE] with_query[,..]]
INSERT INTO table_name [(column_name[,…])]
  { DEFAULT VALUES
  | VALUES {({ expression | DEFAULT }[,…])}[,…]
  | query }
[upsert_clause]
[RETURNING { * | {output_expression [ [AS] output_name ]}[,…]}];

upsert_clause语法

1、ON DUPLICATE UPDATE {{column_name = {expression | DEFAULT}}[,…] | NOTHING }
2、ON CONFLICT [conflict_target]DO conflict_action

1、conflict_target语法

① (index_params)[where_clause]

根据该语法指定表的字段名或表达式等信息，执行时通过该信息找到对应的唯一索引进行冲突检测。

index_params为索引的参数，与CREATE INDEX时的索引参数一致。

{{column_name | (expression)} [COLLATE collation] [opclass] [ASC|DESC] [NULLS{FIRST|LAST}][,…]

但是在upset语句中，不支持指定ASC/DESC、NULLS FIRST/LAST。

where_clause为条件表达式，目前暂不支持where_caluse。

② ON CONSTRAINT name

根据该语法指定表的唯一约束名称，执行时将根据该唯一约束找到对应的唯一索引进行冲突检测，但目前暂不支持该用法。

2、conflict_action语法

① DO UPDATE SET { column_name= { expression |DEFAULT }}[,…][where_clause]

检测到冲突时执行UPDATE。

在SET子句中，可以EXCLUDED来引用要插入的列值，但不支持引用系统列（比如ctid)。例如: UPDATESET c1=EXCLUDE.c1则表示将c1更新为SQL中 insert部分计划插入的c1字段的值;而UPDATE setc1=EXCLUDE.ctid:text 则不支持。

where_clause为条件表达式，用于指定update的条件，是可选节点，但目前暂不支持指定where_clause。

② DO NOTHING

检测到冲突时什么也不做。

说明

ON CONFLICT语法暂不支持在视图、外部表、分区表中使用。
conflict_target用于指定冲突检测的目标，该目标必须能关联到唯一索引。.目前暂不支持不指定conflict_target的用法。
支持 insert的数据类型允许在SQL的insert部分写入。
支持update的数据类型允许在upsext_clause中的update子句中更新。

注意

upsert语句暂不支持以下场景：

SQL中包含 upsert子句和 returning子句
SQL中包含upsert子句和 with子句
update set子句中包含子查询
upsert,子句的conflict_target为空
upsert子句的conflict_target为ON CONSTRAINT name
upsert子句的conflict_target中包含where子句
upsert,子句的conflict_action中包含where子句
upsert子句的conflict_target指定了ASC/DESC 、NULLS FIRSTLAST关键字
upsert语法不支持在分区表、视图、外部表中使用

示例

create table tu1(c1 int unique, c2 int);
insert into tul values(3,3) on conflict(c1) do update set c2=EXCLUDED.c2;--将执行写入
insert into tul values(3,4) on conflict(c1) do update set c2=EXCLUDED.c2;--将执行更新
insert into tul values(3,5) on conflict(e1) do nothing; --将什么都不做

CREATE TABLE test01 (col1 INT PRIMARY KEY, col2 INT);
insert into test01 values(1,1);
INSERT INTO test01(col1) VALUES(1) ON DUPLICATE KEY UPDATE col2 = 2;

VastbaseG100

向表中插入数据

弹框头部