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 ] [, ...] ]

where 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 [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

说明

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

目标列名可以按任何顺序列出。如果根本没有给出列名列表，则默认为表的所有列按其声明的顺序；或者前*N个列名（如果仅由VALUES子句或query提供N个列）。由VALUES子句或query*提供的值与显式或隐式列列表从左到右关联。

显式或隐式列列表中不存在的每一列都将填充默认值，即声明的默认值或（如果不存在）空值。

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

INSERT进入缺少唯一索引的表不会被并发活动阻塞。如果并发会话执行锁定或修改与正在插入的唯一索引值匹配的行，则带有唯一索引的表可能会阻塞；详情请参阅第 64.5 节。ON CONFLICT可用于指定引发唯一约束或排除约束违规错误的替代操作。（请参见下面的ON CONFLICT 子句。）

可选的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还需要对在ON CONFLICT DO UPDATE表达式或*condition*中读取其值的任何列具有SELECT权限。

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

参数

插入

本部分涵盖仅在插入新行时可能用到的参数。专门用于ON CONFLICT子句的参数将单独描述。

with_query

使用 WITH 子句，您可以在 INSERT 查询中按名称引用一个或多个子查询。有关详细信息，请参见第 7.8 节和 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 的标识列，在未指定 OVERRIDING SYSTEM VALUE 或 OVERRIDING USER VALUE 的情况下插入显式值（DEFAULT 除外）是一种错误。（对于定义为 GENERATED BY DEFAULT 的标识列，OVERRIDING SYSTEM VALUE 是正常行为，指定它不会产生任何影响，但 PostgreSQL 允许将其作为扩展。）

OVERRIDING USER VALUE

如果指定此子句，则忽略为标识列提供的所有值，并应用默认序列生成的值。

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

DEFAULT VALUES

所有列都将填充为其默认值，就像为每列显式指定 DEFAULT 一样。（此形式中不允许 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—“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，或一个 DO UPDATE 子句，指定在发生冲突时要执行的 UPDATE 操作的确切详细信息。在 ON CONFLICT DO UPDATE 中，SET 和 WHERE 子句可以使用表的名称（或别名）访问现有行，并使用特殊 excluded 表访问建议插入的行。SELECT 权限要求在目标表中读取相应 excluded 列的任何列上。

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

index_column_name

table_name 列的名称。用于推断仲裁索引。遵循 CREATE INDEX 格式。需要对 index_column_name 拥有 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是插入或更新的行数。oid始终为 0（如果count*正好为 1 且目标表声明为WITH OIDS，则它曾经是分配给插入行的OID，否则为 0，但不再支持创建WITH OIDS表）。

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

说明

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

您可能还想考虑使用MERGE，因为它允许在单个语句中混合INSERT、UPDATE和DELETE。请参见MERGE。

示例

向表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;

要使用多行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中，表tmp_films的列布局与films相同

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

此示例插入到数组列中

-- Create an empty 3x3 gameboard for noughts-and-crosses
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
-- The subscripts in the above example aren't really needed
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子句用于限制实际更新的行（任何未更新的现有行仍将被锁定）

-- Don't update existing distributors based in a certain ZIP code
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';

-- Name a constraint directly in the statement (uses associated
-- index to arbitrate taking the DO NOTHING action)
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

如果可能，插入新分销商；否则不执行任何操作。示例假设已定义唯一索引，该索引约束出现在did列中的值，其中is_active布尔列评估为true

-- This statement could infer a partial unique index on "did"
-- with a predicate of "WHERE is_active", but it could also
-- just use a regular unique constraint on "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    ON CONFLICT (did) WHERE is_active DO NOTHING;

兼容性

INSERT符合 SQL 标准，但RETURNING子句是PostgreSQL扩展，与WITH与INSERT一起使用以及使用ON CONFLICT指定备用操作的能力一样。此外，标准不允许省略列名称列表但并非所有列都由VALUES子句或*query*填充的情况。如果您希望获得比ON CONFLICT更加符合 SQL 标准的语句，请参阅MERGE。

SQL 标准规定，仅当始终存在生成的身份列时，才能指定OVERRIDING SYSTEM VALUE。PostgreSQL 在任何情况下都允许该子句，并在不适用时忽略它。

*query*子句的可能限制在SELECT下有记录。

INSERT ​

语法 ​

说明 ​

参数 ​

插入 ​

ON CONFLICT子句 ​

输出 ​

说明 ​

示例 ​

兼容性 ​

INSERT

语法

说明

参数

插入

`ON CONFLICT`子句

输出

说明

示例

兼容性