INSERT#

INSERT

INSERT — 在一个表中创建新行

大纲

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

描述

INSERT将新行插入到一个表中。我们可以插入一个或者更多由值表达式指定的行，或者插入来自一个查询的零行或者更多行。

目标列的名称可以以任意顺序列出。如果没有给出列名列表，则有两种确定目标列的可能性。第一种是以被声明的顺序列出该表的所有列。另一种可能性是，如果VALUES 子句或者``query``只提供``N``个列，则以被声明的顺序列出该表的前 ``N``列。VALUES 子句或者 ``query``提供的值会被从左至右关联到这些显式或者隐式给出的目标列。

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

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

可选的RETURNING子句让INSERT根据实际被插入的每一行来计算和返回值。这主要用来获取由默认值提供的值，例如一个序列号。不过，允许在其中包括使用该表列的任何表达式。 RETURNING列表的语法与SELECT的输出列表的相同。只有被成功地插入的行才将被返回。

为了向表中插入，你必须具有其上的INSERT特权。

如果一个列列表被指定，你只需要其中的列上的INSERT 特权。

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

参数

插入

这个小节介绍了在只插入新行时可以使用的参数。

``with_query``

WITH子句允许指定一个或者更多子查询，在 INSERT查询中可以用名称引用这些子查询。详见第 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``

要用于被返回列的名称。

输出

成功完成时，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;

兼容性

INSERT符合 SQL 标准，不过 RETURNING子句是一种 OushuDB 扩展，在 INSERT中使用WITH也是。还有，标准不允许省略列名列表但不通过 VALUES子句或者``query``填充所有列的情况。

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

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

OushuDB 产品文档

INSERT

INSERT#