INSERT 语句 – DuckDB - 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

关于此页面

行为准则商标使用指南

关于此页面

本文中