Tip

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将新行插入表中。可以插入由值表达式指定的一或多个行,或查询产生的零或多个行。

目标列名称可以按任何 Sequences 列出。如果根本没有给出任何列名列表,则默认值为表中所有列的声明 Sequences;否则为默认值。如果VALUES子句或* query 仅提供 N 列,则返回第一个 N 列名。 VALUES子句或 query *提供的值与从左到右的显式或隐式列列表相关联。

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

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

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特权。

Parameters

Inserting

本节介绍仅插入新行时可以使用的参数。 ON CONFLICT子句“专有地”使用的参数将单独描述。

  • with_query

    • WITH子句允许您指定一个或多个子查询,这些子查询可以在INSERT查询中按名称引用。有关详情,请参见Section 7.8SELECT
  • query (SELECT语句)也可能包含WITH子句。在这种情况下, with_query 的两个集合都可以在 query *内引用,但是第二个优先级更高,因为它嵌套得更紧密。
  • table_name

    • 现有表的名称(可选,由模式限定)。
  • alias

      • table_name *的替代名称。提供别名后,它将完全隐藏表的实际名称。当ON CONFLICT DO UPDATE定位到名为excluded的表时,这特别有用,因为否则它将被用作表示建议插入的行的特殊表的名称。
  • column_name

    • 表中以* table_name *命名的列的名称。如果需要,可以使用子字段名称或数组下标来限定列名称。 (仅插入到复合列的某些字段中,而其他字段为 null.)当使用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子句指定引发唯一违反或排除约束违反错误的替代操作。对于建议插入的每个单独行,要么 continue 插入,要么如果违反了* conflict_target 指定的 arbiter 约束或索引,则采用替代项 conflict_action *。 ON CONFLICT DO NOTHING只是避免插入行作为其替代操作。 ON CONFLICT DO UPDATE更新与建议作为插入操作插入的行冲突的现有行。

  • conflict_target 可以执行唯一索引推断*。执行推理时,它由一个或多个* index_column_name 列和/或 index_expression 表达式以及一个可选的 index_predicate 组成。所有 table_name 唯一的索引(不考虑 Sequences)都完全包含 conflict_target 指定的列/表达式被推导(选择)为仲裁索引。如果指定 index_predicate *,则作为推理的进一步要求,它必须满足仲裁程序索引。请注意,这意味着如果存在满足其他所有条件的索引,则将推断出非局部唯一索引(不带谓词的唯一索引)(因此,ON CONFLICT将使用该索引)。如果推理尝试失败,则会引发错误。

ON CONFLICT DO UPDATE确保原子INSERTUPDATE的结果;如果没有独立的错误,即使在高并发情况下,也可以保证这两个结果之一。这也称为* 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中的SETWHERE子句可以使用表的名称(或别名)访问现有行,并可以使用特殊的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格式。必须对出现在index_expression中的任何列具有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的值。尽管执行ON CONFLICT DO UPDATE动作时所有行将被锁定,但仅此表达式返回true的行将被更新。请注意,在将condition 标识为要更新的候选对象之后,最后评估 condition *。

请注意,不支持将排除约束作为ON CONFLICT DO UPDATE的仲裁器。在所有情况下,仅支持NOT DEFERRABLE约束和唯一索引作为仲裁器。

带有ON CONFLICT DO UPDATE子句的INSERT是“确定性”语句。这意味着该命令不允许多次影响任何单个现有行;发生这种情况时,将引发基数违反错误。建议插入的行不应在受仲裁器索引或约束约束的属性方面相互重复。

请注意,当前不支持将INSERTON CONFLICT DO UPDATE子句应用于已分区表以更新冲突行的分区键,从而要求将该行移至新分区。

Tip

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

Outputs

成功完成后,INSERT命令将返回以下形式的命令标签:

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

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

Notes

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

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');

本示例将表tmp_films中的某些行从表tmp_films插入到表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;

增加 ManagementAcme 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。该示例假定已定义一个唯一索引,该索引将约束出现在is_active布尔列求值为true的行的子集上did列中的值:

-- 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 扩展,使用WITHINSERT的能力以及使用ON CONFLICT指定替代动作的能力。此外,标准不允许列名列表被省略,但不是所有列都由VALUES子句或* query *填充的情况。

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

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