描述

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

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

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

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

如果表缺少唯一索引，则并发操作不会阻塞对表的 INSERT 操作。 对于具有唯一索引的表，如果并发会话执行锁定或修改与插入的唯一索引值匹配的行的操作，则可能会被阻塞。 可以使用 ON CONFLICT 指定替代操作以避免唯一约束或排除约束违规错误。 （请参见下文的 ON CONFLICT Clause。）

可选的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特权。

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

要对多表进行插入， 你可以使用 INSERT ALL 语句。 在一个多表插入中，将把根据子查询返回的行派生出的计算行插入到一个或多个表中。

参数

INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=t1.c+1;

UPDATE t1 SET c=c+1 WHERE a=1;
      

输出

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

考虑以下 CREATE TABLE 语句创建的表，然后运行 ltsql 客户端中显示的语句，结果如下所示：

CREATE TABLE test (
  id INT NOT NULL,
  name CHAR(16),
  age INT DEFAULT 0,
  PRIMARY KEY(id)
);

lightdb@testdb=# INSERT INTO test VALUES (1, 'Old name', '18');
INSERT 0 1

lightdb@testdb=# INSERT INTO test VALUES (2, 'Old name', '19') ON DUPLICATE KEY UPDATE name = 'New name';
INSERT 0 1

lightdb@testdb=# SELECT * FROM test;
 id |       name       | age 
----+------------------+-----
  1 | Old name         |  18
  2 | Old name         |  19
(2 rows)

lightdb@testdb=# INSERT INTO test VALUES (2, 'Old name', '20') ON DUPLICATE KEY UPDATE name = 'New name', age = test.age + 5;
INSERT 0 1

lightdb@testdb=# SELECT * FROM test;
 id |       name       | age 
----+------------------+-----
  1 | Old name         |  18
  2 | New name         |  24
(2 rows)

lightdb@testdb=# INSERT INTO test VALUES (2, 'Old name', '20') ON DUPLICATE KEY UPDATE name = 'New name', age = excluded.age;
INSERT 0 1

lightdb@testdb=# SELECT * FROM test;
 id |       name       | age 
----+------------------+-----
  1 | Old name         |  18
  2 | New name         |  20
(2 rows)

lightdb@testdb=# INSERT INTO test VALUES (2, 'Old name', '20') ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);
INSERT 0 1

lightdb@testdb=# SELECT * FROM test;
 id |       name       | age 
----+------------------+-----
  1 | Old name         |  18
  2 | Old name         |  20
(2 rows)

lightdb@testdb=# INSERT INTO test VALUES (2, 'New name', '29') ON DUPLICATE KEY UPDATE name = name, age = VALUES(age);
INSERT 0 1

lightdb@testdb=# SELECT * FROM test;
 id |       name       | age 
----+------------------+-----
  1 | Old name         |  18
  2 | Old name         |  29
(2 rows)

可以通过使用 insert all 语句去对多个表进行插入, 同时也支持通过条件来进行插入，案例如下：

  CREATE TABLE T1(a int , b text);
  CREATE TABLE T1_1(a int , b text);
  CREATE TABLE T1_2(a int , b text);

  -- 多表插入
  INSERT ALL INTO T1_1 VALUES(1,1) INTO T1_2 VALUES(2,2) SELECT * FROM DUAL;
  INSERT ALL INTO T1_1 VALUES(a,b) INTO T1_2 VALUES(a%100, b) SELECT * FROM T1;
  -- 带条件的多表插入
  INSERT ALL WHEN a > 2 THEN INTO T1_1 ELSE INTO T1_2 SELECT * FROM T1;

MySQL兼容模式下使用Insert Ignore:

create database mydb with lightdb_syntax_compatible_type 'mysql';
\c mydb;
create table t( a int primary key, b int);
INSERT IGNORE INTO t(a,b) values(1,1),(2,2),(1,1);

兼容性

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

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

ON DUPLICATE KEY UPDATE 子句是 LightDB 对 SQL 标准的扩展。 它仅在数据库的语法兼容类型为 mysql 时受支持。 有关详细信息，请参见 lightdb_syntax_compatible_type。

INSERT ALL 语句 是 LightDB 对标准 SQL 的一个扩展. 它只在 ORACLE 兼容模式下的数据库中使用。 有关兼容模式的详细信息，请参见 lightdb_syntax_compatible_type。

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