VastbaseE100

INSERT

INSERT - 在表中创建新行

语法格式

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER} VALUE ]
    { 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 子句或 query 仅提供 N 列，则为第一个 N 列名称。 VALUES 子句或query 提供的值从左到右与显式或隐式列列表相关联。

显式或隐式列列表中不存在的每个列都将填充一个默认值，无论是声明的默认值还是null（如果没有）。

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

ON CONFLICT 可用于指定引发唯一约束或排除约束违规错误的替代操作。（见下文 ON CONFLICT Clause。）

可选的 RETURNING 子句使 INSERT根据实际插入的每一行计算和返回值（如果使用了 ON CONFLICT DO UPDATE 子句，则更新或更新）。这主要用于获取默认提供的值，例如序列号。但是，允许使用表的列的任何表达式。RETURNING 列表的语法与 SELECT的输出列表的语法相同。仅返回已成功插入或更新的行。例如，如果一行被锁定但由于未满足ON CONFLICT DO UPDATE … WHERE 子句 condition而未更新，则不会返回该行。

您必须对表具有 INSERT 特权才能插入其中。如果存在 ON CONFLICT DO UPDATE，则还需要表上的 UPDATE 权限。

如果指定了列列表，则只需要列出列的 INSERT 权限。同样，当指定 ON CONFLICT DO UPDATE 时，您只需要列出要更新的列的 UPDATE 权限。但是，对于在 ON CONFLICT DO UPDATE 表达式或 condition 中读取其值的任何列， ON CONFLICT DO UPDATE 也需要 SELECT 权限。

使用 RETURNING 子句需要 SELECT 中提到的所有列的 SELECT 权限。如果使用query 子句从查询中插入行，则当然需要对查询中使用的任何表或列具有 SELECT特权。

参数说明

插入

本节介绍仅插入新行时可能使用的参数说明。与 ON CONFLICT子句专用的参数说明将单独描述。

• with_query

  WITH 子句允许您指定可在 INSERT查询中按名称引用的一个或多个子查询。有关详细信息，请参阅 SELECT。

  query （ SELECT 语句）也可能包含 WITH 子句。在这种情况下，可以在 query中引用两组 with_query ，但第二组优先，因为它更紧密地嵌套。

• table_name

  现有表的名称（可选择模式限定）。

• alias

  table_name 的替代名称。提供别名时，它会完全隐藏表的实际名称。当 ON CONFLICT DO UPDATE 以一个名为 excluded 的表为目标时，这尤其有用，因为否则这将被视为表示建议插入的行的特殊表的名称。

• column_name

  由 table_name命名的表中列的名称。如果需要，可以使用子字段名称或数组下标限定列名称。（仅插入复合列的某些字段会使其他字段为空。）使用 ON CONFLICT DO UPDATE引用列时，请勿在目标列的规范中包含表的名称。例如， INSERT INTO table_name … ON CONFLICT DO UPDATE SET table_name.col = 1无效（这遵循 UPDATE 的一般行为）。

• OVERRIDING SYSTEM VALUE

  如果没有此子句，为定义为 GENERATED ALWAYS 的标识列指定显式值（ DEFAULT除外）是错误的。该条款优先于该限制。

• OVERRIDING USER VALUE

  如果指定了此子句，则忽略为定义为 GENERATED BY DEFAULT的标识列提供的任何值，并应用默认的序列生成值。

  例如，在表之间复制值时，此子句很有用。写入 INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 将从 tbl1 复制 tbl2中不是标识列的所有列，而 tbl2 中的标识列的值将由与 tbl2 关联的序列生成。

• DEFAULT VALUES

  所有列都将使用其默认值填充。 （此表格中不允许使用 OVERRIDING 条款。）

• expression

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

• DEFAULT

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

• query

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

• output_expression

  在插入或更新每行之后由 INSERT 命令计算和返回的表达式。表达式可以使用table_name 指定的表的任何列名。写 * 以返回插入或更新的行的所有列。

• output_name

  用于返回列的名称。

ON CONFLICT条款

可选的 ON CONFLICT子句指定了引发唯一违规或排除约束违规错误的备用操作。对于建议插入的每个单独的行，插入继续进行，或者，如果违反conflict_target 指定的仲裁器约束或索引，则采用替代 conflict_action 。 ON CONFLICT DO NOTHING 只是避免插入行作为替代操作。 ON CONFLICT DO UPDATE更新与建议插入的行冲突的现有行作为其替代操作。

conflict_target 可以执行唯一索引推断。执行推理时，它由一个或多个index_column_name 列和/或 index_expression 表达式以及一个可选的index_predicate 组成。所有 table_name 唯一索引，不考虑顺序，完全包含conflict_target 指定的列/表达式被推断（选择）为仲裁索引。如果指定了index_predicate，则作为推理的进一步要求，它必须满足仲裁索引。请注意，这意味着如果满足所有其他条件的索引可用，则将推断出非部分唯一索引（没有谓词的唯一索引）（因此由ON CONFLICT 使用）。如果尝试推理不成功，则会引发错误。

ON CONFLICT DO UPDATE 保证原子 INSERT 或 UPDATE结果;如果没有独立错误，即使在高并发性下，也可以保证这两种结果中的一种。这也称为UPSERT- “更新或插入”。

• conflict_target

  通过选择仲裁索引，指定 ON CONFLICT对哪个冲突采取替代操作。要么执行唯一索引推断，要么明确命名约束。对于 ON CONFLICT DO NOTHING ，指定 conflict_target是可选的;省略时，处理与所有可用约束（和唯一索引）的冲突。对于 ON CONFLICT DO UPDATE ，必须提供 conflict_target 。

• conflict_action

  conflict_action 指定备用 ON CONFLICT 操作。它可以是 DO NOTHING，也可以是 DO UPDATE 子句，指定在发生冲突时要执行的 UPDATE操作的确切详细信息。 ON CONFLICT DO UPDATE 中的 SET 和 WHERE子句可以使用表的名称（或别名）访问现有行，并使用特殊 excluded表访问建议插入的行。目标表中任何读取相应 excluded 列的列都需要 SELECT权限。

  请注意，所有每行 BEFORE INSERT 触发器的效果都反映在 excluded值中，因为这些效果可能导致从插入中排除的行。

• index_column_name

  table_name 列的名称。用于推断仲裁索引。遵循 CREATE INDEX 格式。 SELECT上的 SELECT 特权是必需的。

• 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子句当前不支持更新冲突行的分区键，因此需要将行移动到新分区。

注意

通常最好使用唯一索引推断，而不是直接使用 ON CONFLICT ON CONSTRAINT constraint_name命名约束。当基础索引以重叠方式被另一个或多或少等价的索引替换时，推断将继续正常工作，例如在删除索引被替换之前使用CREATE UNIQUE INDEX …CONCURRENTLY 时。

输出

成功完成后， INSERT 命令将返回表单的命令标记

INSERT oid count

count 是插入或更新的行数。如果 count 正好为1，并且目标表具有OID，则 oid是分配给插入行的OID。必须插入而不是更新单行。否则 oid 为零。

如果 INSERT 命令包含 RETURNING 子句，则结果将类似于 SELECT语句的结果，该语句包含 RETURNING列表中定义的列和值，通过命令插入或更新的行计算。

注意事项

如果指定的表是分区表，则每行都将路由到相应的分区并插入其中。如果指定的表是分区，则如果其中一个输入行违反分区约束，则会发生错误。

示例

在表 films 中插入一行：

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

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

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

此示例将 DEFAULT 子句用于日期列，而不是指定值：

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

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

INSERT INTO films DEFAULT VALUES;

要使用multirow 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');

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

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

此示例插入到数组列中：

-- 为 noughts-and-crosses 游戏创建一个空的 3x3 棋盘
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
-- 实际上可以不用上面例子中的下标
INSERT INTO tictactoe (game, board)
    VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');

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

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

增加管理Acme Corporation帐户的销售人员的销售额，并将整个更新行与当前时间一起记录在日志表中：

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

根据需要插入或更新新的经销商。假设已定义唯一索引，该索引限制出现在 did列中的值。请注意，特殊的 excluded 表用于引用最初建议插入的值：

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

插入分发服务器，或者对现有的排除行（具有匹配的约束列的行或行插入触发器之前的列触发的行）存在时，不对建议插入的行执行任何操作。示例假定已定义唯一索引，该索引限制出现在did 列中的值：

INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
    ON CONFLICT (did) DO NOTHING;

根据需要插入或更新新的经销商。示例假定已定义唯一索引，该索引限制出现在did 列中的值。 WHERE子句用于限制实际更新的行（但未更新的任何现有行仍将被锁定）：

-- 根据一个特定的 ZIP 编码更新 distributors
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
    ON CONFLICT (did) DO UPDATE
    SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
    WHERE d.zipcode <> '21201';

-- 直接在语句中命名一个约束（使用相关的索引来判断是否做
-- DO NOTHING 动作）
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

如果插入新的经销商可能;否则 DO NOTHING。示例假定已定义唯一索引，该索引将 condition 列中出现的值约束在is_active 布尔列的求值为 true 的行的子集上：

-- 这个语句可能推断出一个在 "did" 上带有谓词 "WHERE is_active"
-- 的部分唯一索引，但是它可能也只是使用了 "did" 上的一个常规唯一约束
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    ON CONFLICT (did) WHERE is_active DO NOTHING;