INSERT 语句 – DuckDB 数据库

搜索快捷键 cmd + k | ctrl + k

文档 / SQL / 语句

INSERT 语句

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

示例

将值 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);

语法

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;

这些语句成功完成，并使表保留行 <i: 1, j: 42>。

`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;

示例

一个使用 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;

限制

当使用 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

复合主键

当多个列需要作为唯一性约束的一部分时，请使用包含所有相关列的单个 PRIMARY KEY 子句

CREATE TABLE t1 (id1 INTEGER, id2 INTEGER, val1 DOUBLE, PRIMARY KEY(id1, id2));
INSERT OR REPLACE INTO t1
    VALUES (1, 2, 3);
INSERT OR REPLACE INTO t1
    VALUES (1, 2, 4);

定义冲突目标

可以提供冲突目标，格式为 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;

`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_times_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

示例

语法