Postgresql 中文操作指南

INSERT

INSERT——在表中创建新行

Synopsis

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

Description

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

目标列名称可以以任何顺序列出。如果完全不指定列名称的列表，则默认值为表的所有列按照其声明的顺序；或 N 列名称，如果仅通过 VALUES 子句或 query 提供 N 个列。由 VALUES 子句或 query 提供的值与显式或隐式列列表从左到右关联。

在显式或隐式列列表中不出现的每一列都将填充一个默认值，即其声明的默认值，如果没有默认值，则填充 null。

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

INSERT 的表中没有唯一索引不会被并发活动阻塞。如果并发会话执行锁定或修改与正在插入的唯一索引值匹配的行，则具有唯一索引的表可能会被阻塞；详细的信息请参阅 Section 64.5 。可以 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 还需要对 ON CONFLICT DO UPDATE 表达式或 condition 中读取其值的任何列有 SELECT 权限。

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

Parameters

Inserting

本节涵盖只在插入新行时可以使用的参数。与 ON CONFLICT 子句使用 exclusively 参数被单独描述。

  • with_query

    • WITH 子句允许您指定一个或多个可在 INSERT 查询中通过名称引用的子查询。有关详细信息，请参见 Section 7.8 和 SELECT 。

    • query 语句 ( SELECT 语句) 可能还会包含一个 WITH 子句。在这种情况下， with_query 的两组都可以在 query 内被引用，但第二组的优先级更高，因为它嵌套得更紧密。

  • table_name

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

  • alias

    • table_name 的代替名称。当提供别名时，它会完全隐藏表的实际名称。当 ON CONFLICT DO UPDATE 针对名为 excluded 的表时，这特别有用，因为其他情况下将把 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 Clause

可选的 ON CONFLICT 子句指定了一个不同的操作来引发唯一违规或排除约束违规错误。对于提议插入的每一行，要么进行插入，要么如果违反了 conflict_target 指定的 arbiter 约束或索引，则执行不同的 conflict_action 。 ON CONFLICT DO NOTHING 只会避免将其作为替代操作插入一行。 ON CONFLICT DO UPDATE 将更新与提议插入的行冲突的现有行为作为其替代操作。

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

ON CONFLICT DO UPDATE 保证了原子 INSERT 或 UPDATE 结果；只要没有独立的错误，这两个结果之一就能得到保证，即使在高并发的情况下也是如此。这也称为 UPSERT ——“更新或插入”。

  • conflict_target

    • 通过选择 arbiter indexes 来指定 ON CONFLICT 对哪些冲突执行替代操作。要么执行 unique index inference ，要么明确命名一个约束。对于 ON CONFLICT DO NOTHING ，可以不指定 conflict_target ；如果省略，则将处理与所有可用的约束（和唯一索引）的冲突。对于 ON CONFLICT DO UPDATE ，必须提供一个 conflict_target must 。

  • 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 格式。需要在 index_column_name 上拥有 SELECT 权限。

  • index_expression

    • 类似于 index_column_name ，但用于推断 table_name 列上表达式，它出现在索引定义中（不是简单列）。遵循 CREATE INDEX 格式。要求对任何出现在 table_name 中的字段都有 SELECT 权限。

  • collation

    • 指定时，要求相应的 index_column_name 或 index_expression 在推断期间使用特定排序规则才能进行匹配。通常情况下会省略此项，因为排序规则通常不影响是否会发生约束冲突。遵循 CREATE INDEX 格式。

  • opclass

    • 指定时，要求相应的 index_column_name 或 index_expression 在推断期间使用特定运算符类才能进行匹配。通常情况下会省略此项，因为 equality 语义通常在类型的运算符类中是等效的，或因为相信定义的唯一索引有适当的等式定义就足够了。遵循 CREATE INDEX 格式。

  • index_predicate

    • 用于允许推断部分唯一索引。可以推断满足谓词的任何索引（实际上不需要是部分索引）。遵循 CREATE INDEX 格式。要求对任何出现在 index_predicate 中的字段都有 SELECT 权限。

  • constraint_name

    • 明确地按名称指定仲裁 constraint ，而不是推断约束或索引。

  • condition

    • 返回类型 boolean 值的表达式。只有该表达式返回 true 的行将被更新，尽管在执行 ON CONFLICT DO UPDATE 操作时所有行都将被锁定。请注意，在冲突被确定为更新候选之后，最后对 condition 进行评估。

请注意，在使用 ON CONFLICT DO UPDATE 时，排除约束不受支持作为仲裁。在所有情况下，只有 NOT DEFERRABLE 约束和唯一索引才受支持作为仲裁。

带有 ON CONFLICT DO UPDATE 子句的 INSERT 是“确定性”语句。这意味着不允许该命令对任何单个现有行造成多次影响；在出现此情况时会引发基数冲突错误。建议插入的行为在由仲裁索引或约束约束的属性方面彼此不重复。

请注意，目前不支持将应用于已分区表的 INSERT 的 ON CONFLICT DO UPDATE 子句更新冲突行的分区键，使其要求将该行移动到新分区。

Tip

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

Outputs

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

INSERT oid count

count 是已插入或更新的行数。 oid 始终为 0（如果 count 正好为 1 且目标表已声明为 WITH OIDS ，则它过去是分配给插入行的 OID，否则为 0，但是不支持再创建表 WITH OIDS ）。

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

Notes

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

您也许还需要考虑使用 MERGE ，因为它允许在单个语句中组合 INSERT 、 UPDATE 和 DELETE 。请参见 MERGE 。

Examples

插入一行到表 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');

此示例将一些行从具有与 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;

如果可能的话，插入新的分销商；否则 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;

Compatibility

INSERT 符合 SQL 标准，但 RETURNING 子句是 PostgreSQL 扩展，并且 WITH 与 INSERT 一起使用以及使用 ON CONFLICT 指定备用操作的能力也是如此。此外，在省略列名称列表的情况下，但并非所有列都从 VALUES 子句或 query 填充的情况，这是标准不允许的。如果您更喜欢一个更符合 SQL 标准的语句 @{s22」，请参阅 MERGE 。

SQL 标准规定，只有在始终存在一个生成的标识列的情况下才能指定 OVERRIDING SYSTEM VALUE 。在任何情况下，PostgreSQL 都允许该子句，如果它不适用，则忽略它。

query 子句的可能限制在 SELECT 下作了说明。