INSERT Statement

Search Shortcut cmd + k | ctrl + k

Documentation / SQL / Statements

INSERT Statement

INSERT 语句将新数据插入到表中。

Examples

将值1, 2, 3插入到tbl中：

INSERT INTO tbl
    VALUES (1), (2), (3);

将查询结果插入表中：

INSERT INTO tbl
    SELECT * FROM other_tbl;

将值插入到i列中，并将默认值插入到其他列中：

INSERT INTO tbl (i)
    VALUES (1), (2), (3);

显式地将默认值插入到列中：

INSERT INTO tbl (i)
    VALUES (1), (DEFAULT), (3);

假设 tbl 有一个主键/唯一约束，在冲突时不执行任何操作：

INSERT OR IGNORE INTO tbl (i)
    VALUES (1);

或者用新值更新表格：

INSERT OR REPLACE INTO tbl (i)
    VALUES (1);

Syntax

INSERT INTO 将新行插入表中。可以插入由值表达式指定的一行或多行，或者插入由查询产生的零行或多行。

插入列顺序

可以提供一个可选的插入列顺序，这可以是BY POSITION（默认）或BY NAME。每个不在显式或隐式列列表中的列都将用默认值填充，要么是其声明的默认值，要么是NULL（如果没有默认值）。

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

`INSERT INTO ... [BY POSITION]`

值插入表中列的顺序由列声明的顺序决定。也就是说，由VALUES子句或查询提供的值与列列表从左到右关联。这是默认选项，可以使用BY POSITION选项显式指定。例如：

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl
    VALUES (5, 42);

指定 BY POSITION 是可选的，并且等同于默认行为：

INSERT INTO tbl
    BY POSITION
    VALUES (5, 42);

要使用不同的顺序，可以将列名作为目标的一部分提供，例如：

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl (b, a)
    VALUES (5, 42);

添加 BY POSITION 会导致相同的行为：

INSERT INTO tbl
    BY POSITION (b, a)
    VALUES (5, 42);

这将把 5 插入到 b 中，并将 42 插入到 a 中。

`INSERT INTO ... BY NAME`

使用BY NAME修饰符，SELECT语句的列名列表将与表的列名进行匹配，以确定值应插入表中的顺序。这允许在表的列顺序与SELECT语句中的值顺序不同或某些列缺失的情况下进行插入。

例如：

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl BY NAME (SELECT 42 AS b, 32 AS a);
INSERT INTO tbl BY NAME (SELECT 22 AS b);
SELECT * FROM tbl;

a	b
32	42
NULL	22

需要注意的是，当使用INSERT INTO ... BY NAME时，SELECT语句中指定的列名必须与表中的列名匹配。如果列名拼写错误或表中不存在该列，将会发生错误。SELECT语句中缺失的列将使用默认值填充。

`ON CONFLICT` 子句

可以使用ON CONFLICT子句来对由UNIQUE或PRIMARY KEY约束引起的冲突执行特定操作。以下示例展示了此类冲突的示例：

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84);

这会引发一个错误：

Constraint Error: Duplicate key "i: 1" violates primary key constraint.

表格将包含首先插入的行：

SELECT * FROM tbl;

i	j
1	42

这些错误信息可以通过显式处理冲突来避免。 DuckDB支持两个这样的子句：ON CONFLICT DO NOTHING 和 ON CONFLICT DO UPDATE SET ...。

`DO NOTHING` 子句

DO NOTHING 子句会导致错误被忽略，并且不会插入或更新值。例如：

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO NOTHING;

这些语句成功完成，并在表中留下行。

`INSERT OR IGNORE INTO`

INSERT OR IGNORE INTO ... 语句是 INSERT INTO ... ON CONFLICT DO NOTHING 的简写语法替代。例如，以下语句是等价的：

INSERT OR IGNORE INTO tbl
    VALUES (1, 84);
INSERT INTO tbl
    VALUES (1, 84) ON CONFLICT DO NOTHING;

`DO UPDATE` 子句 (Upsert)

DO UPDATE 子句导致 INSERT 在冲突的行上变为 UPDATE。后面的 SET 表达式决定了这些行如何更新。表达式可以使用特殊的虚拟表 EXCLUDED，其中包含行的冲突值。您可以选择提供一个额外的 WHERE 子句，可以排除某些行不进行更新。不符合此条件的冲突将被忽略。

因为我们需要一种方式来引用待插入的元组和现有的元组，所以我们引入了特殊的EXCLUDED限定符。当提供了EXCLUDED限定符时，引用指向待插入的元组，否则，它指向现有的元组。这个特殊的限定符可以在ON CONFLICT子句的WHERE子句和SET表达式中使用。

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl VALUES (1, 42);
INSERT INTO tbl VALUES (1, 52), (1, 62) ON CONFLICT DO UPDATE SET j = EXCLUDED.j;

Examples

一个使用 DO UPDATE 的例子如下：

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;

i	j
1	84

重新排列列并使用BY NAME也是可行的：

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl (j, i)
    VALUES (168, 1)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl
    BY NAME (SELECT 1 AS i, 336 AS j)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;

i	j
1	336

`INSERT OR REPLACE INTO`

INSERT OR REPLACE INTO ... 语句是 INSERT INTO ... DO UPDATE SET c1 = EXCLUDED.c1, c2 = EXCLUDED.c2, ... 的简短语法替代。也就是说，它将现有行的每一列更新为待插入行的新值。例如，给定以下输入表：

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);

这些语句是等价的：

INSERT OR REPLACE INTO tbl
    VALUES (1, 84);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl (j, i)
    VALUES (84, 1)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl BY NAME
    (SELECT 84 AS j, 1 AS i)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;

Limitations

当使用ON CONFLICT ... DO UPDATE子句并且发生冲突时，DuckDB内部会为不受冲突影响的行列分配NULL值，然后重新分配它们的值。如果受影响的列使用了NOT NULL约束，这将触发NOT NULL constraint failed错误。例如：

CREATE TABLE t1 (id INTEGER PRIMARY KEY, val1 DOUBLE, val2 DOUBLE NOT NULL);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, val1 DOUBLE);
INSERT INTO t1
    VALUES (1, 2, 3);
INSERT INTO t2
    VALUES (1, 5);

INSERT INTO t1 BY NAME (SELECT id, val1 FROM t2)
    ON CONFLICT DO UPDATE
    SET val1 = EXCLUDED.val1;

这会失败并出现以下错误：

Constraint Error: NOT NULL constraint failed: t1.val2

定义冲突目标

冲突目标可以作为ON CONFLICT (conflict_target)提供。这是一组定义了索引或唯一性/键约束的列。如果省略冲突目标，或者目标是表上的PRIMARY KEY约束。

除非使用DO UPDATE并且表上有多个唯一/主键约束，否则指定冲突目标是可选的。

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER UNIQUE, k INTEGER);
INSERT INTO tbl
    VALUES (1, 20, 300);
SELECT * FROM tbl;

i	j	k
1	20	300

INSERT INTO tbl
    VALUES (1, 40, 700)
    ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k;

i	j	k
1	20	1400

INSERT INTO tbl
    VALUES (1, 20, 900)
    ON CONFLICT (j) DO UPDATE SET k = 5 * EXCLUDED.k;

i	j	k
1	20	4500

当提供了冲突目标时，你可以使用WHERE子句进一步过滤，所有冲突都应满足该子句。

INSERT INTO tbl
    VALUES (1, 40, 700)
    ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k WHERE k < 100;

同一键上的多个元组冲突

Limitations

目前，DuckDB的ON CONFLICT DO UPDATE功能仅限于在已提交的数据和新插入的（事务本地的）数据之间强制执行约束。换句话说，不支持在同一键上有多个元组冲突的情况。如果新插入的数据有重复行，将会抛出错误消息，或者可能发生意外行为。这也包括仅在新插入的数据内部的冲突。

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84), (1, 168)
    ON CONFLICT DO UPDATE SET j = j + EXCLUDED.j;

这将返回以下消息。

Invalid Input Error: ON CONFLICT DO UPDATE can not update the same row twice in the same command.
Ensure that no rows proposed for insertion within the same command have duplicate constrained values

为了解决这个问题，使用DISTINCT ON来强制唯一性。例如：

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    SELECT DISTINCT ON(i) i, j FROM VALUES (1, 84), (1, 168) AS t (i, j)
    ON CONFLICT DO UPDATE SET j = j + EXCLUDED.j;
SELECT * FROM tbl;

i	j
1	126

`RETURNING` 子句

RETURNING 子句可用于返回插入行的内容。如果某些列在插入时被计算，这可能很有用。例如，如果表包含一个自动递增的主键，那么 RETURNING 子句将包括自动创建的主键。这在生成列的情况下也很有用。

可以明确选择返回部分或全部列，并且可以选择使用别名重命名它们。也可以返回任意的非聚合表达式，而不仅仅是返回一个列。可以使用*表达式返回所有列，并且除了*返回的所有列之外，还可以返回列或表达式。

例如：

CREATE TABLE t1 (i INTEGER);
INSERT INTO t1
    SELECT 42
    RETURNING *;

i
42

一个更复杂的例子，其中包括在RETURNING子句中的表达式：

CREATE TABLE t2 (i INTEGER, j INTEGER);
INSERT INTO t2
    SELECT 2 AS i, 3 AS j
    RETURNING *, i * j AS i_times_j;

i	j	i乘以j
2	3	6

下一个示例展示了RETURNING子句更有帮助的情况。首先，创建一个带有主键列的表。然后创建一个序列，以便在插入新行时递增该主键。当我们插入表时，我们并不知道序列生成的值，因此返回这些值是有价值的。更多信息，请参见CREATE SEQUENCE页面。

CREATE TABLE t3 (i INTEGER PRIMARY KEY, j INTEGER);
CREATE SEQUENCE 't3_key';
INSERT INTO t3
    SELECT nextval('t3_key') AS i, 42 AS j
    UNION ALL
    SELECT nextval('t3_key') AS i, 43 AS j
    RETURNING *;

i	j
1	42
2	43

Examples

Syntax

插入列顺序

`INSERT INTO ... [BY POSITION]`

`INSERT INTO ... BY NAME`

`ON CONFLICT` 子句

`DO NOTHING` 子句

`INSERT OR IGNORE INTO`

`DO UPDATE` 子句 (Upsert)

Examples

`INSERT OR REPLACE INTO`

Limitations

定义冲突目标

同一键上的多个元组冲突

Limitations

`RETURNING` 子句

关于此页面

本文内容