文档首页/ 数据仓库服务 GaussDB(DWS)/ 开发指南/ 实时数仓开发指南(9.1.0.x)/ 实时数仓语法/ UPSERT
更新时间：2024-12-10 GMT+08:00
查看PDF
分享

UPSERT

功能描述

HStore兼容UPSERT语法，向表中添加一行或多行数据。当出现主键或者唯一约束冲突时更新或者忽略冲突的数据。

  • 如需使用实时数仓能力，请在DWS控制台创建集群时，选择存算一体1：4云盘规格，了解更多参见存算一体规格。
  • 创建DWS集群选择1：8云盘规格为标准数仓，1：4云盘规格为实时数仓，以下内容描述的“实时数仓”、“标准数仓”即通过以上规格区分。

注意事项

  • 目标表上必须包含主键或者唯一索引才可以执行UPSERT的冲突更新语句。
  • 与列存一样，当UPSERT触发更新操作时，当前版本HStore上的更新操作始终先DELETE再INSERT。
  • 对于并发UPSERT场景，传统列存储格式由于同时操作相同CU时会产生锁冲突所以并发性能较差，对于HStore表由于不需要阻塞等待，并发UPSERT性能可达到列存的百倍以上。

语法格式

表1 UPSERT语法格式

语法格式

冲突更新

冲突忽略

第一种：不指定索引

 
INSERT INTO ON DUPLICATE KEY UPDATE
 
INSERT IGNORE
INSERT INTO ON CONFLICT DO NOTHING

第二种：从指定列名或者约束上可以推断唯一约束

 
INSERT INTO ON CONFLICT(...) DO UPDATE SET
INSERT INTO ON CONFLICT ON CONSTRAINT con_name DO UPDATE SET
 
INSERT INTO ON CONFLICT(...) DO NOTHING
INSERT INTO ON CONFLICT ON CONSTRAINT con_name DO NOTHING

参数说明

第一种不指定索引。会在所有主键或唯一索引上检查冲突，有冲突就会忽略或者更新。

第二种指定索引。会从ON CONFLICT子句中指定列名、包含列名的表达式或者约束名上推断主键或者唯一索引。

  • 唯一索引推断

    对于第二种语法形式，通过指定列名或者约束名推断主键或者唯一索引。列名可以是单一列名，或者由多个列名组成的表达式，比如column1，column2，column3。

  • UPDATE子句

    UPDATE子句可以通过VALUES(colname)或者EXCLUDED.colname引用插入的数据。EXCLUDED表示因冲突原本该排除的数据行。

  • WHERE子句
    • 用于在数据冲突时，判断是否满足指定条件。如果满足，则更新冲突数据。否则忽略。
    • 只有第二种语法形式的冲突更新语法可以指定WHERE子句。即 INSERT INTO ON CONFLICT(...) DO UPDATE SET WHERE

示例

创建表reason_upsert并向表中插入数据： 
1
2
3
4
5
6
7
 
CREATE TABLE reason_upsert
(
  a    int primary key,
  b    int,
  c    int
)WITH(ORIENTATION=COLUMN, ENABLE_HSTORE=ON);
INSERT INTO reason_upsert VALUES (1, 2, 3);
忽略冲突的数据： 
1
 
INSERT INTO reason_upsert  VALUES (1, 4, 5),(2, 6, 7) ON CONFLICT(a) DO NOTHING;
更新冲突的数据： 
1
 
INSERT INTO reason_upsert  VALUES (1, 4, 5),(3, 8, 9) ON CONFLICT(a) DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c;
父主题： 实时数仓语法

相关文档