INSERT

功能描述

向表中添加一行或多行数据。

注意事项

• 只有拥有表INSERT权限的用户，才可以向表中插入数据。
• 如果使用RETURNING子句，用户必须要有该表的SELECT权限。
• 如果使用QUERY子句插入来自查询里的数据行，用户还需要拥有在查询里使用的表的SELECT权限。
• 如果使用OVERWRITE子句覆盖式插入数据，用户还需要拥有该表的SELECT和TRUNCATE权限。
• 当连接到TD兼容的数据库时，td_compatible_truncation参数设置为on时，将启用超长字符串自动截断功能，在后续的insert语句中（不包含外表的场景下），对目标表中char和varchar类型的列上插入超长字符串时，系统会自动按照目标表中相应列定义的最大长度对超长字符串进行截断。
  

  如果向字符集为字节类型编码（SQL_ASCII，LATIN1等）的数据库中插入多字节字符数据（如汉字等），且字符数据跨越截断位置，这种情况下，按照字节长度自动截断，自动截断后会在尾部产生非预期结果。如果用户有对于截断结果正确性的要求，建议用户采用UTF8等能够按照字符截断的输入字符集作为数据库的编码集。

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT [/*+ plan_hint */] [ IGNORE | OVERWRITE ] INTO table_name [ partition_clause ] [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES
    | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] 
    | query }
    [ ON DUPLICATE KEY duplicate_action | ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

where partition_clause can be:
    PARTITION ( partition_name )
    | PARTITION FOR ( partition_key_value [, ...] )

and duplicate_action can be:

    UPDATE { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
           } [, ...]

and conflict_target can be one of:

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

and conflict_action is one of:

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

参数说明

• WITH [ RECURSIVE ] with_query [, ...]

  用于声明一个或多个可以在主查询中通过名字引用的子查询，相当于临时表。

  如果声明了RECURSIVE，那么允许SELECT子查询通过名字引用它自己。

  其中with_query的详细格式为：

  with_query_name [ ( column_name [, ...] ) ] AS
  ( {select | values | insert | update | delete} )

  – with_query_name指定子查询生成的结果集名字，在查询中可使用该名称访问子查询的结果集。

  – column_name指定子查询结果集中显示的列名。

  – 每个子查询可以是SELECT，VALUES，INSERT，UPDATE或DELETE语句。

• plan_hint子句

  以/*+ */的形式在关键字后，用于对指定语句块生成的计划进行hint调优，详细用法请参见使用Plan Hint进行调优。

• IGNORE

  用于主键或者唯一约束冲突时忽略冲突的数据。

  详细介绍参见UPSERT。

• OVERWRITE

  用于标识覆盖式插入方式，使用此种插入方式执行结束后，目标原数据被清空，只存在新插入的数据。

  OVERWRITE支持指定列插入的功能，其他列为默认值，若无默认值则为NULL。

  

  • OVERWRITE不要和INSERT INTO这类实时写入的操作并发，否则实时写入数据有被意外清理的风险。
  • OVERWRITE适用于大批量数据导入场景，不建议用于少量数据的插入场景。
  • 避免对同一张表执行并发insert overwrite操作，否则会出现类似报错“tuple concurrently updated.”。
  • 如果集群正在扩缩容，且INSERT OVERWRITE的写入表需要执行数据重分布，则INSERT OVERWRITE会清除当前数据，并自动将插入的数据按扩缩容后的节点来进行数据分布。如果INSERT OVERWRITE和该表的数据重分布过程同时执行，INSERT OVERWRITE会中断该表的数据重分布过程。
  • OVERWRITE支持覆盖式插入OBS外表，此方式会删除原目录中数据文件。
  • 外表支持OVERWRITE单分区，支持Value多级分区，该功能仅9.1.0.100及以上版本支持。

    示例：INSERT OVERWRITE into table_name partition for (p1_value, p2_value...) ...

• table_name

  要插入数据的目标表名。

  取值范围：已存在的表名。

• AS

  用于给目标表table_name指定别名。alias即为别名的名字。

• column_name

  目标表中的字段名：

  • 字段名可以用子字段名或者数组下标修饰。
  • 没有在字段列表中出现的每个字段，将由系统默认值，或者声明时的默认值填充，若都没有则用NULL填充。例如，向一个复合类型中的某些字段插入数据，其他字段将是NULL。
  • 目标字段（column_name）可以按顺序排列。如果没有列出任何字段，则默认全部字段，且顺序为表声明时的顺序。
  • 如果value子句和query中只提供了N个字段，则目标字段为前N个字段。
  • value子句和query提供的值在表中从左到右关联到对应列。

  取值范围：已存在的字段名。

• partition_name

  分区名。仅8.2.1及以上集群版本支持该选项。

  取值范围：已存在的分区名。

• partition_key_value

  分区键值。

  通过PARTITION FOR ( partition_key_value [, ...] )子句指定的这一组值，可以唯一确定一个分区。

  取值范围：需要进行重命名分区的分区键的取值范围。

• expression

  赋予对应column的一个有效表达式或值：

  • 向表中字段插入单引号时需要使用单引号自身进行转义。
  • 如果插入行的表达式不是正确的数据类型，系统试图进行类型转换，若转换不成功，则插入数据失败，系统返回错误信息。

  示例：

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

  CREATE TABLE tt01 (id int,content varchar(50)); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE INSERT INTO tt01 values (1,'Jack say ''hello'''); INSERT 0 1 INSERT INTO tt01 values (2,'Rose do 50%'); INSERT 0 1 INSERT INTO tt01 values (3,'Lilei say ''world'''); INSERT 0 1 INSERT INTO tt01 values (4,'Hanmei do 100%'); INSERT 0 1 SELECT * FROM tt01; id | content ----+------------------- 3 | Lilei say 'world' 4 | Hanmei do 100% 1 | Jack say 'hello' 2 | Rose do 50% (4 rows)

• DEFAULT

  对应字段名的缺省值。如果没有缺省值，则为NULL。

• query

  一个查询语句（SELECT语句），将查询结果作为插入的数据。

• ON DUPLICATE KEY

  用于主键或者唯一约束冲突时更新冲突的数据。

  duplicate_action指定更新列和更新的数据。

  详细介绍参见UPSERT。

• ON CONFLICT

  用于主键或者唯一约束冲突时忽略或者更新冲突的数据。

  conflict_target用于指定列名index_column_name 、包含多个列名的表达式index_expression或者约束名字constraint_name。用于从列名、包含多个列名的表达式或者约束名推断是否有唯一索引。其中index_column_name和index_expression遵循CREATE INDEX的索引列格式。

  conflict_action指定主键或者唯一约束冲突时执行的策略。有两种：

  • DO NOTHING冲突忽略。
  • DO UPDATE SET冲突更新。 后面指定更新列和更新的数据。

  详细介绍参见UPSERT。

• RETURNING

  返回实际插入的行，RETURNING列表的语法与SELECT的输出列表一致。

• output_expression

  INSERT命令在每一行都被插入之后用于计算输出结果的表达式。

  取值范围：该表达式可以使用table的任意字段。可以使用*返回被插入行的所有字段。

• output_name

  字段的输出名称。

  取值范围：字符串，符合标识符命名规范。

示例

创建表reason_t1：

1
2
3
4
5
6

CREATE TABLE reason_t1
(
    TABLE_SK          INTEGER               ,
    TABLE_ID          VARCHAR(20)           ,
    TABLE_NA          VARCHAR(20)
);

向表中插入一条记录：

1

INSERT INTO reason_t1(TABLE_SK, TABLE_ID, TABLE_NA) VALUES (1, 'S01', 'StudentA');

向表中插入一条记录，和上一条语法等效：

1

INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA');

向表中插入TABLE_SK小于1的记录：

1

INSERT INTO reason_t1 SELECT * FROM reason_t1 WHERE TABLE_SK < 1;

向表中插入多条记录：

1
2
3
4
5
6
7
8

INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA'),(2, 'T01', 'TeacherA'),(3, 'T02', 'TeacherB');
SELECT * FROM reason_t1 ORDER BY 1;
 TABLE_SK | TABLE_ID | TABLE_NAME
----------+----------+------------
        1 |      S01 |   StudentA
        2 |      T01 |   TeacherA
        3 |      T02 |   TeacherB
(3 rows)

使用INSERT OVERWRITE更新表中的数据，即覆盖式插入数据：

1
2
3
4
5
6

INSERT OVERWRITE INTO reason_t1 values (4, 'S02', 'StudentB');
SELECT * FROM reason_t1 ORDER BY 1;
 TABLE_SK | TABLE_ID | TABLE_NAME
----------+----------+------------
        4 |      S02 |   StudentB
(1 rows)

将表reason_t1的数据插入到表reason_t1中：

INSERT INTO reason_t1 SELECT * FROM reason_t1;

对独立的字段明确缺省值：

INSERT INTO reason_t1 VALUES (5, 'S03', DEFAULT);

将一个表中的部分数据插入到另一个表中：先通过WITH子查询得到一张临时表temp_t，然后将临时表temp_t中的所有数据插入另一张表reason_t1中：

WITH temp_t AS (SELECT * FROM reason_t1) INSERT INTO reason_t1 SELECT * FROM temp_t ORDER BY 1;

向分区表的指定分区插入数据：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE test_range_row(a int, d int)
DISTRIBUTE BY hash(a) PARTITION BY RANGE(d)
(
    PARTITION p1 values LESS THAN (60),
    PARTITION p2 values LESS THAN (75),
    PARTITION p3 values LESS THAN (90),
    PARTITION p4 VALUES LESS THAN (maxvalue)
);
INSERT OVERWRITE INTO test_range_row PARTITION(p1) VALUES(55,51);
INSERT OVERWRITE INTO test_range_row PARTITION(p3) VALUES(85,80);

DELETE FROM test_range_row PARTITION(p1);