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

通过显式处理冲突可以避免这些错误信息。 Goose 支持两种子句：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 子句且发生冲突时，Goose 内部会先将该行中未受冲突影响的列赋值为 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