INSERT — 在一个表中创建新行
[ WITH [ RECURSIVE ]with_query
[, ...] ] INSERT INTOtable_name
[ ASalias
] [ (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
) } [ COLLATEcollation
] [opclass
] [, ...] ) [ WHEREindex_predicate
] ON CONSTRAINTconstraint_name
并且conflict_action
是以下之一: DO NOTHING DO UPDATE SET {column_name
= {expression
| DEFAULT } | (column_name
[, ...] ) = [ ROW ] ( {expression
| DEFAULT } [, ...] ) | (column_name
[, ...] ) = (sub-SELECT
) } [, ...] [ WHEREcondition
]
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
权限。
如果一个列列表被指定,你只需要其中的列上的INSERT
特权。类似地,在指定了ON CONFLICT DO UPDATE
时,你只
需要被列出要更新的列上的UPDATE
特权。不过,
ON CONFLICT DO UPDATE
还要求其值被
ON CONFLICT DO UPDATE
表达式或者
condition
使用的列上的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
的标识列指定显式值(除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
是一种“确定性的”
语句。这表明不允许该命令影响任何单个现有行超过一次,如果发生则会
发生一个基数违背错误。要插入的行不应该在仲裁者索引或约束所限制的
属性上相重复。
使用唯一索引推断通常比使用ON CONFLICT ON
CONSTRAINT
constraint_name
直接提名一个约束更好。当底层索引
被以重叠方式替换成另一个或多或少等效的索引时,推断将能继续正确地
工作,例如在删除要被替换的索引之前使用
CREATE UNIQUE INDEX ... CONCURRENTLY
。
成功完成时,INSERT
命令会返回以下形式的命令标签:
INSERToid
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有介绍。