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提供的值会被从左至右关联到这些显式或者隐式 给出的目标列。
每一个没有出现在显式或者隐式列列表中的列都将被默认填充，如果为该列 声明过默认值则用默认值填充，否则用空值填充。
如果任意列的表达式不是正确的数据类型，将会尝试自动类型转换。
ON CONFLICT可以用来指定发生唯一约束或者排除约束 违背错误时的替换动作（见下文的ON CONFLICT 子句）。
可选的RETURNING子句让INSERT根据 实际被插入（如果使用了ON CONFLICT DO UPDATE子句， 可能是被更新）的每一行来计算和返回值。这主要用来获取由默认值提供 的值，例如一个序列号。不过，允许在其中包括使用该表列的任何表达式。 RETURNING列表的语法与 SELECT的输出 列表的相同。只有被成功地插入或者更新的行才将被返回。例如，如果一 行被锁定但由于不满足ON CONFLICT DO UPDATE ... WHEREclause 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_queryWITH子句允许指定一个或者更多子查询，在 INSERT查询中可以用名称引用这些子查询。详见 第 7.8 节以及 SELECT
。
query（SELECT语句）也可以包含一个 WITH子句。在这种情况下 query中可以引用 两组with_query，但是第二个优先级更 高（因为它被嵌套更近）。
table_name一个已有表的名称（可以被模式限定）。
aliastable_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 SYSTEM VALUE或OVERRIDING USER VALUE是错误的。（对于定义 为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
构成。所有刚好包含 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_actionconflict_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子句更新冲突行的分区键，因为那样会让行移动到新的分区中。
使用唯一索引推断通常比使用ON CONFLICT ON CONSTRAINTconstraint_name直接提名一个约束更好。当底层索引被以重叠方式替换成另一个或多或少等效的索引时，推断将能继续正确地工作，例如在删除要被替换的索引之前使用CREATE UNIQUE INDEX ... CONCURRENTLY。
成功完成时，INSERT命令会返回以下形式的命令标签：
INSERT oid count
count是被插入或更新的行数。 oid总是0（过去，如果count恰好为1， 并且目标表被声明为WITH OIDS，则它是分配给插入行的OID， 否则为0， 但现在已不再支持创建WITH OIDS表）。
如果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有介绍。
备案信息： 粤ICP备15087711号-2
Copyright © 2008-2024 啊嘎哇在线工具箱 All Rights.
本站所有资料来源于网络，版权归原作者所有，仅作学习交流使用，如不慎侵犯了您的权利，请联系我们。