INSERT

INSERT
Prev	Up	SQL 命令	Home	Next

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

其中 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提供的值会被从左至右关联到这些显式或者隐式给出的目标列。

每一个没有出现在显式或者隐式列列表中的列都将被默认填充，如果为该列声明过默认值则用默认值填充，否则用空值填充。

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

ON CONFLICT可以用来指定发生唯一约束或者排除约束违背错误时的替换动作（见下文的ON CONFLICT 子句）。

可选的RETURNING子句让INSERT根据实际被插入（如果使用了ON CONFLICT DO UPDATE子句，可能是被更新）的每一行来计算和返回值。这主要用来获取由默认值提供的值，例如一个序列号。不过，允许在其中包括使用该表列的任何表达式。 RETURNING列表的语法与SELECT的输出列表的相同。只有被成功地插入或者更新的行才将被返回。例如，如果一行被锁定但由于不满足ON CONFLICT DO UPDATE ... WHERE clause condition没有被更新，该行将不被返回。

为了向表中插入，你必须具有其上的INSERT特权。如果存在ON CONFLICT DO UPDATE子句，还要求该表上的UPDATE特权。

如果一个列列表被指定，你只需要其中的列上的INSERT 特权。类似地，在指定了ON CONFLICT DO UPDATE时，你只需要被列出要更新的列上的UPDATE特权。不过， ON CONFLICT DO UPDATE还要求其值被 ON CONFLICT DO UPDATE表达式或者 condition使用的列上的SELECT特权。

参数

插入

这个小节介绍了在只插入新行时可以使用的参数。 专门用于ON CONFLICT子句的参数会单独介绍。

with_query

WITH子句允许指定一个或者更多子查询，在 INSERT查询中可以用名称引用这些子查询。详见 Section 7.8以及SELECT。

query （SELECT语句）也可以包含一个 WITH子句。在这种情况下 query中可以引用两组with_query，但是第二个优先级更高（因为它被嵌套更近）。

table_name

一个已有表的名称（可以被模式限定）。

alias

table_name 的替补名称。当提供了一个别名时，它会完全隐藏掉表的实际名称。当ON CONFLICT DO UPDATE的目标是一个被排除的表时这特别有用，因为那将被当作表示要被插入行的特殊表的名称。

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构成。所有刚好包含 conflict_target指定的列/表达式的table_name唯一索引（不管顺序）都会被推断为（选择为）仲裁者索引。如果指定了 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，也可以是一个指定在冲突情况下要被执行的UPDATE动作细节的DO UPDATE子句。ON CONFLICT DO UPDATE中的SET和 WHERE子句能够使用该表的名称（或者别名）访问现有的行，并且可以用特殊的被排除 表访问要插入的行。这个动作要求被排除 列所在目标表的任何列上的SELECT特权。

注意所有行级BEFORE INSERT触发器的效果都会反映在被排除值中，因为那些效果可能会让该行避免被插入。

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子句更新冲突行的分区键，因为那样会让行移动到新的分区中。

Tip

使用唯一索引推断通常比使用ON CONFLICT ON CONSTRAINT constraint_name直接提名一个约束更好。当底层索引被以重叠方式替换成另一个或多或少等效的索引时，推断将能继续正确地工作，例如在删除要被替换的索引之前使用CREATE UNIQUE INDEX ... CONCURRENTLY。

输出

成功完成时，INSERT命令会返回以下形式的命令标签：

INSERT oid count

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

如果INSERT命令包含RETURNING子句，其结果会类似于包含RETURNING列表中定义的列和值的 SELECT语句，这些结果是由该命令在被插入或更新行上计算得到。

注解

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

示例

向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中，两个表具有相同的列布局：

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;

酌情插入或者更新新的 distributor。假设已经定义了一个唯一索引来约束出现在did列中的值。注意，特殊的 excluded表被用来引用原来要插入的值：

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

插入一个 distributor，或者在一个被排除的行（具有一个匹配约束的列或者会让行级前（或者后）插入触发器引发的列的行）存在时不处理要插入的行。例子假设已经定义了一个唯一触发器来约束出现在did列中的值：

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

酌情插入或者更新新的 distributor。例子假设已经定义了一个唯一触发器来约束出现在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;

如果可能就插入新的 distributor，否则DO NOTHING。例子假设已经定义了一个唯一索引，它约束让is_active 布尔列为true的行子集上did列中的值：

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

兼容性

INSERT符合 SQL 标准，不过 RETURNING子句是一种 PostgreSQL扩展，在 INSERT中使用WITH也是，用ON CONFLICT指定一个替代动作也是扩展。还有，标准不允许省略列名列表但不通过 VALUES子句或者query填充所有列的情况。

SQL标准指定只有存在一个总是会生成值的标识列时才能指定OVERRIDING SYSTEM VALUE。而PostgreSQL在任何情况下都允许这个子句，并且在不适用时会忽略它。

query子句可能的限制在 SELECT有介绍。

Prev	Up	Next
IMPORT FOREIGN SCHEMA	Home	LISTEN

INSERT

Synopsis

描述

参数

插入

ON CONFLICT 子句

Tip

输出

注解

示例

兼容性

`ON CONFLICT` 子句