功能描述
向表中添加一行或多行数据。
注意事项
只有拥有表INSERT 权限的用户，才可以向表中插入数据。用户被授予insert any table
权限，相当于用户对除系统模式之外的任何模式具有USAGE 权限，并且拥有这些模式下表
的INSERT 权限
如果使用RETURNING 子句，用户必须要有该表的SELECT 权限。
如果使用ON DUPLICATE KEY UPDATE，用户必须要有该表的SELECT、UPDATE
权限，唯一约束（主键或唯一索引）的SELECT 权限。
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
1179
如果使用query 子句插入来自查询里的数据行，用户还需要拥有在查询里使用的表的
SELECT 权限。
当连接到TD 兼容的数据库时，td_compatible_truncation 参数设置为on 时，将启用超长
字符串自动截断功能，在后续的insert 语句中（不包含外表的场景下），对目标表中char
和varchar 类型的列上插入超长字符串时，系统会自动按照目标表中相应列定义的最大长度
对超长字符串进行截断。
说明：如果向字符集为字节类型编码（SQL_ASCII，LATIN1 等）的数据库中插
入多字节字符数据（如汉字等），且字符数据跨越截断位置，这种情况下，按照字节长度自
动截断，自动截断后会在尾部产生非预期结果。如果用户有对于截断结果正确性的要求，建
议用户采用UTF8 等能够按照字符截断的输入字符集作为数据库的编码集。
语法格式
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT [/*+ plan_hint */] INTO table_name [partition_clause] [ AS alias ]
[ ( column_name [, ...] ) ]
{ DEFAULT VALUES
| VALUES {( { expression | DEFAULT } [, ...] ) }[, ...]
| query }
[ ON DUPLICATE KEY UPDATE { NOTHING | { column_name = { expression | DEFAULT } }
[, ...] [ WHERE condition ] }]
[ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ]
[ 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 )
} [, ...]
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
1180
[ WHERE condition ]
参数说明
WITH [ RECURSIVE ] with_query [, …]
用于声明一个或多个可以在主查询中通过名称引用的子查询，相当于临时表。
如果声明了RECURSIVE，那么允许SELECT 子查询通过名称引用它自己。
其中with_query 的详细格式为：
with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ]
( {select | values | insert | update | delete} )
– with_query_name 指定子查询生成的结果集名称，在查询中可使用该名称访问子查询
的结果集。
– column_name 指定子查询结果集中显示的列名。
– 每个子查询可以是SELECT，VALUES，INSERT，UPDATE 或DELETE 语句。
– 用户可以使用MATERIALIZED / NOT MATERIALIZED 对CTE 进行修饰。
如果声明为MATERIALIZED，WITH 查询将被物化，生成一个子查询结果集的拷贝，
在引用处直接查询该拷贝，因此WITH 子查询无法和主干SELECT 语句进行联合优化（如
谓词下推、等价类传递等），对于此类场景可以使用NOT MATERIALIZED 进行修饰，如
果WITH 查询语义上可以作为子查询内联执行，则可以进行上述优化。
如果用户没有显示声明物化属性则遵守以下规则：如果CTE 只在所属主干语句中被引
用一次，且语义上支持内联执行，则会被改写为子查询内联执行，否则以CTE Scan 的方式
物化执行。
说明：INSERT ON DUPLICATE KEY UPDATE 不支持WITH 及WITH
RECURSIVE 子句。
plan_hint 子句
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
1181
以/*+ */的形式在INSERT 关键字后，
用于对INSERT 对应的语句块生成的计划进行hint
调优，详细用法请参见章节使用Plan Hint 进行调优。每条语句中只有第一个/*+ plan_hint */
注释块会作为hint 生效，里面可以写多条hint。
table_name
要插入数据的目标表名。
取值范围：已存在的表名。
partition_clause
指定分区插入操作
PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } |
SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) }
关键字详见SELECT 一节介绍
如果value 子句的值和指定分区不一致，会抛出异常。
示例详见CREATE TABLE SUBPARTITION
column_name
目标表中的字段名：
字段名可以有子字段名或者数组下标修饰。
没有在字段列表中出现的每个字段，将由系统默认值，
或者声明时的默认值填充，
若都
没有则用NULL 填充。例如，向一个复合类型中的某些字段插入数据的话，其他字段将是
NULL。
目标字段（column_name）
可以按顺序排列。
如果没有列出任何字段，则默认全部字段，
且顺序为表声明时的顺序。
如果value 子句和query 中只提供了N 个字段，则目标字段为前N 个字段。
value 子句和query 提供的值在表中从左到右关联到对应列。
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
1182
取值范围：已存在的字段名。
expression
赋予对应column 的一个有效表达式或值：
如果是INSERT ON DUPLICATE KEY UPDATE 语句下，expression 可以为
VALUES(column_name)或EXCLUDED.column_name 用来表示引用冲突行对应的
column_name 字段的值。需注意，其中VALUES(column_name)不支持嵌套在表达式中（例
如VALUES(column_name)+1），但EXCLUDED 不受此限制。
向表中字段插入单引号“ ' ”时需要使用单引号自身进行转义。
如果插入行的表达式不是正确的数据类型，系统试图进行类型转换，
若转换不成功，则
插入数据失败，系统返回错误信息。
DEFAULT
对应字段名的缺省值。如果没有缺省值，则为NULL。
query
一个查询语句（SELECT 语句），将查询结果作为插入的数据。
RETURNING
返回实际插入的行，RETURNING 列表的语法与SELECT 的输出列表一致。注意：
INSERT ON DUPLICATE KEY UPDATE 不支持RETURNING 子句。
output_expression
INSERT 命令在每一行都被插入之后用于计算输出结果的表达式。
取值范围：该表达式可以使用table 的任意字段。可以使用*返回被插入行的所有字段。
output_name
字段的输出名称。
取值范围：字符串，符合标识符命名规范。
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
1183
ON DUPLICATE KEY UPDATE
对于带有唯一约束（UNIQUE INDEX 或PRIMARY KEY）的表，如果插入数据违反唯
一约束，则对冲突行执行UPDATE 子句完成更新，对于不带唯一约束的表，则仅执行插入。
UPDATE 时，若指定NOTHING 则忽略此条插入，可通过“EXCLUDE.” 或者“VALUES()”
来选择源数据相应的列。
支持触发器，触发器执行顺序由实际执行流程决定：
执行insert：触发before insert、after insert 触发器。
执行update：触发before insert、before update、after update 触发器。
执行update nothing：触发before insert 触发器。
不支持延迟生效（DEFERRABLE）的唯一约束或主键。
如果表中存在多个唯一约束，
如果所插入数据违反多个唯一约束，
对于检测到冲突的第
一行进行更新，其他冲突行不更新（检查顺序与索引维护具有强相关性，
一般先创建的索引
先进行冲突检查）。
如果插入多行，这些行均与表中同一行数据存在唯一约束冲突，
则按照顺序，
第一条执
行插入或更新，之后依次执行更新。
主键、唯一索引列不允许UPDATE。
不支持列存，不支持外表、内存表。
expression 支持使用子查询表达式，其语法与功能同UPDATE。子查询表达式中支持使
用“EXCLUDED.”来选择源数据相应的列。
ON CONFLICT 子句
可选的ON CONFLICT 子句为出现唯一性违背或排除约束违背错误时提供另一种可供
选择的动作。对于每一个要插入的行，不管是插入进行下去还是由conflict_target 指定的
一个仲裁者约束或者索引被违背，
都会采取可供选择的conflict_action。ON CONFLICT DO
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
1184
NOTHING 简单地把避免插入行。ON CONFLICT DO UPDATE 则会更新与要插入的行冲
突的已有行。
conflict_target 可以执行唯一索引推断。在执行推断时，它由一个或者多个
index_column_name 列或者index_expression 表达式以及一个可选的index_predicate 构成。
所有刚好包含conflict_target 指定的列/表达式的table_name 唯一索引（不管顺序）都会被
推断为（选择为）仲裁者索引。如果指定了index_predicate，它必须满足仲裁者索引（也
是推断过程的一个进一步的要求）。注意这意味着如果有一个满足其他条件的非部分唯一
索引
（没有谓词的唯一索引）
可用，
它将被推断为仲裁者
（并且会被ON CONFLICT 使用）
。
如果推断尝试不成功，则会发生一个错误。
ON CONFLICT DO UPDATE 保证一个原子的INSERT 或者UPDATE 结果。在没有无
关错误的前提下，这两种结果之一可以得到保证，即使在很高的并发度也能保证。这也可
以被称作UPSERT — “UPDATE 或INSERT”。
conflict_target：通过选择仲裁者索引来指定哪些行与ON CONFLICT 在其上采取
可替代动作的行相冲突。要么执行唯一索引推断，要么显式命名一个约束。
对于
ON CONFLICT DO NOTHING 来说，它对于指定一个conflict_target 是可选的。在
被省略时，与所有有效约束（以及唯一索引）的冲突都会被处理。对于ON
CONFLICT DO UPDATE，必须提供一个conflict_target。
conflict_action：conflict_action 指定一个可替换的ON CONFLICT 动作。它可以是
DO NOTHING，也可以是一个指定在冲突情况下要被执行的UPDATE 动作细节
的DO UPDATE 子句。ON CONFLICT DO UPDATE 中的SET 和WHERE 子句能
够使用该表的名称（或者别名）访问现有的行，
并且可以用特殊的被排除表访问
要插入的行。这个动作要求被排除列所在目标表的任何列上的SELECT 特权。
注意所有行级BEFORE INSERT 触发器的效果都会反映在被排除值中，因为那些效果
可能会让该行避免被插入。
index_column_name：
一个table_name 列的名称。它被用来推断仲裁者索引。它遵
循CREATE INDEX 格式。这要求index_column_name 上的SELECT 特权。
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
1185
index_expression：和index_column_name 类似，但是被用来推断出现在索引定义
中的table_name 列（非简单列）上的表达式。遵循CREATE INDEX 格式。这要
求任何出现在index_expression 中的列上的SELECT 特权。
collation：指定时，强制相应的index_column_name 或index_expression 使用一种
特定的排序规则以便在推断期间能被匹配上。通常会被省略，因为排序规则通常
不会影响约束违背的发生。遵循CREATE INDEX 格式。
opclass：指定时，强制相应的index_column_name 或index_expression 使用特定的
操作符类以便在推断期间能被匹配上。通常会被省略，因为相等语义在一种类型
的操作符类之间都是等价的，
或者因为足以信任已定义的唯一索引具有适当的相
等定义。遵循CREATE INDEX 格式。
index_predicate：用于允许推断部分唯一索引。任何满足该谓词（不一定需要真的
是部分索引）的索引都能被推断。遵循CREATE INDEX 格式。这要求任何出现
在index_predicate 中的列上的SELECT 特权。
constraint_name：用名称显式地指定一个仲裁者约束，而不是推断一个约束或者
索引。
condition：一个能返回boolean 值的表达式。只有让这个表达式返回true 的行才将
被更新，不过在采用ON CONFLICT DO UPDATE 动作时所有的行都会被锁定。
注意condition 会被最后计算，即一个冲突被标识为要更新的候选对象之后。
注意不支持把排除约束作为ON CONFLICT DO UPDATE 的仲裁者。
在所有的情况中，
只支持NOT DEFERRABLE 约束和唯一索引作为仲裁者。
带有ON CONFLICT DO UPDATE 子句的INSERT 是一种“确定性的” 语句。这表明
不允许该命令影响任何单个现有行超过一次，如果发生则会发生一个基数违背错误。要插
入的行不应该在仲裁者索引或约束所限制的属性上相重复。
注意，
当前不支持用分区表上的INSERT 的ON CONFLICT DO UPDATE 子句更新冲突
行的分区键，因为那样会让行移动到新的分区中。
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
1186
示例
--创建表tpcds.reason_t2。
gbase=#CREATE TABLE tpcds.reason_t2
(
r_reason_sk
integer,
r_reason_id
character(16),
r_reason_desc
character(100)
);
--向表中插入一条记录。
gbase=#INSERT INTO tpcds.reason_t2(r_reason_sk, r_reason_id, r_reason_desc)
VALUES (1, 'AAAAAAAABAAAAAAA', 'reason1');
--向表中插入一条记录，和上一条语法等效。
gbase=#INSERT INTO tpcds.reason_t2 VALUES (2, 'AAAAAAAABAAAAAAA', 'reason2');
--向表中插入多条记录。
gbase=#INSERT INTO tpcds.reason_t2 VALUES (3, 'AAAAAAAACAAAAAAA','reason3'),(4,
'AAAAAAAADAAAAAAA', 'reason4'),(5, 'AAAAAAAAEAAAAAAA','reason5');
--向表中插入tpcds.reason 中r_reason_sk 小于5 的记录。
gbase=#INSERT INTO tpcds.reason_t2 SELECT * FROM tpcds.reason WHERE r_reason_sk
<5;
--对表创建唯一索引
gbase=#CREATE UNIQUE INDEX reason_t2_u_index ON tpcds.reason_t2(r_reason_sk);
--向表中插入多条记录，如果冲突则更新冲突数据行中r_reason_id 字段为
'BBBBBBBBCAAAAAAA'。
gbase=#INSERT INTO tpcds.reason_t2 VALUES (5, 'BBBBBBBBCAAAAAAA','reason5'),(6,
'AAAAAAAADAAAAAAA', 'reason6') ON DUPLICATE KEY UPDATE r_reason_id =
'BBBBBBBBCAAAAAAA';
--删除表tpcds.reason_t2。
gbase=#DROP TABLE tpcds.reason_t2;
优化建议
VALUES
通过insert 语句批量插入数据时，建议将多条记录合并入一条语句中执行插入，以提高
数据加载性能。例如，INSERT INTO sections VALUES (30, 'Administration', 31, 1900)、(40,
'Development', 35, 2000)、(50, 'Development' , 60 , 2001)。