INSERT

在表中创建新行。

概要

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table [( column [, ...] )]
   {DEFAULT VALUES | VALUES ( {expression | DEFAULT} [, ...] ) [, ...] | query}
   [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 ]

描述

INSERT 将新行插入表中。可以插入由值表达式指定的一或多个行，或查询产生的零或多个行。

目标列名称可以按任何顺序列出。如果根本没有给出任何列名列表，则默认为表中的列按声明的顺序排列。VALUES 子句或查询提供的值与从左到右的显式或隐式列列表相关联。

在显式或隐式列列表中不存在的每个列都将填充一个默认值，该默认值可以是其声明的默认值，如果没有默认值，则为 NULL。

如果任何列的表达式的数据类型都不正确，则将尝试自动类型转换。

YMatrix 支持 INSERT INTO ... VALUES ... ON CONFLICT ... DO ... 的语法来支持对 HEAP 表数据进行 UPSERT 操作。限制：使用 UPSERT 功能时，建表的唯一约束或索引必须包含分布键。同时，UPSERT 不支持在不同实例间移动数据，不支持更新分布键。更多 UPSERT 使用方法请见数据分批合并场景（UPSERT）。此语句只适用于 HEAP 表。

可选的 RETURNING 子句使 INSERT 根据实际插入的每一行计算并返回值。这对于获取默认提供的值（例如序列号）很有用。但是，允许使用表列的任何表达式。RETURNING 列表的语法与 SELECT 的输出列表的语法相同。

您必须对表具有 INSERT 特权才能插入表中。指定列列表后，仅需要对列出的列具有 INSERT 特权。使用 RETURNING 子句需要 RETURNING 中提到的所有列都具有 SELECT 特权。如果提供查询以插入查询中的行，则必须对查询中引用的任何表或列具有 SELECT 特权。

注意！
UPSERT 不是一个 SQL 语句，而是一个在时序场景中为便于数据分批上传而支持的更新 / 插入操作。

输出

成功完成后，INSERT 命令将返回以下形式的命令标记：

INSERT oid count

count 是插入的行数。如果 count 恰好为 1，并且目标表具有 OID，则 OID 是分配给插入行的 OID。否则，OID 为零。

参数

插入

with_query

WITH 子句允许您指定一个或多个子查询，这些子查询可以在 INSERT 查询中按名称引用。
对于包含 WITH 子句的 INSERT 命令，该子句只能包含 SELECT 语句，而 WITH 子句不能包含数据修改命令（INSERT，UPDATE 或 DELETE）。
查询（SELECT 语句）也可能包含 WITH 子句。在这种情况下，可以在 INSERT 查询中引用两套 with_query，但是第二套优先，因为它的嵌套更紧密。

table

现有表的名称（可以由模式(Schema)限定）。

column

表中列的名称。如果需要，可以使用子字段名称或数组下标来限定列名称。（仅插入到复合列的某些字段中，未指定的字段为空。）

DEFAULT VALUES

所有列均将填充其默认值。

expression

要分配给相应列的表达式或值。

DEFAULT

相应的列将填充其默认值。

query

提供要插入的行的查询（SELECT 语句）。有关语法的说明，请参见 SELECT 语句。

output_expression

插入每行后，由 INSERT 命令计算并返回的表达式。该表达式可以使用表的任何列名称。写入 * 返回插入行的所有列。

output_name

用于返回的列的名称。

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触发器的效果都会反映在被排除值中，因为那些效果可能会让该行避免被插入。

注解

要将数据插入分区表中，请指定根分区表，即使用 CREATE TABLE 命令创建的表。您还可以在 INSERT 命令中指定分区表的叶子表。如果数据对于指定的叶子表无效，则返回错误。不支持在 INSERT 命令中指定不是叶子表的子表。不支持在分区表的任何子表上执行其他 DML 命令，例如 UPDATE 和 DELETE。这些命令必须在根分区表（使用 CREATE TABLE 命令创建的表）上执行。

示例

在表 films 中插入一行：

INSERT INTO films VALUES ('UA502', 'Bananas', 105, 
'1971-07-13', 'Comedy', '82 minutes');

在此示例中，省略了 length 列，因此它将具有默认值：

INSERT INTO films (code, title, did, date_prod, kind) VALUES 
('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

本示例对 date_prod 列使用 DEFAULT 子句，而不是指定值：

INSERT INTO films VALUES ('UA502', 'Bananas', 105, DEFAULT, 
'Comedy', '82 minutes');

要插入完全由默认值组成的行：

INSERT INTO films DEFAULT VALUES;

要使用多行 VALUES 语法插入多行：

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

本示例从表 films 中向表 films 中插入一些行，其列布局与 films 相同：

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < 
'2004-05-07';

在表 distributors 中插入一行，返回由 DEFAULT 子句生成的序列号：

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;

在 HEAP 表实现 UPSERT：

// 创建测试表
CREATE TABLE conditions (
  tag_id      text  NOT NULL,
  ts          timestamptz   not null,
  location    text              ,
  temperature double precision  ,
  humidity    double precision
) DISTRIBUTED BY (tag_id);
// 创建唯一索引
CREATE UNIQUE INDEX ON conditions(tag_id,ts);
// 插入测试数据
INSERT INTO conditions(tag_id,ts,location) VALUES('tag1','2022-07-19 00:00:00','office') ON CONFLICT(tag_id,ts) DO UPDATE
SET  location = excluded.location,temperature = excluded.temperature,humidity = excluded.humidity;
INSERT INTO conditions(tag_id,ts,temperature) VALUES('tag1','2022-07-19 00:00:00',70.2) ON CONFLICT(tag_id,ts) DO UPDATE
SET  location = excluded.location,temperature = excluded.temperature,humidity = excluded.humidity;
INSERT INTO conditions(tag_id,ts,humidity) VALUES('tag1','2022-07-19 00:00:00',50.1) ON CONFLICT(tag_id,ts) DO UPDATE
SET  location = excluded.location,temperature = excluded.temperature,humidity = excluded.humidity;
// 查看测试数据
mydb=# SELECT * FROM conditions;
 tag_id |           ts           | location | temperature | humidity
--------+------------------------+----------+-------------+----------
 tag1   | 2022-07-19 00:00:00+08 | office   |             |
(1 row)
Time: 16.340 ms

兼容性

INSERT 符合 SQL 标准。标准不允许列名列表被省略，但不是所有的列都由 VALUES 子句或查询填充的情况。

SELECT 记录了 query 子句的可能限制。