DuckDB：精通Insert语句处理数据冲突

本文介绍DuckDB insert语句用法，包括常规的批量插入，尤其是插入数据冲突的处理，最后还提及returning子句的用法，每个用法提供示例说明。

insert插入数据

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

## 批量插入
INSERT INTO tbl
    VALUES (1), (2), (3);
## 从查询插入
INSERT INTO tbl
    SELECT * FROM other_tbl;
## 包括缺省值
INSERT INTO tbl (i)
    VALUES (1), (DEFAULT), (3);

数据冲突处理

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

结果仅（1，42）成功插入，并抛出错误：

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

`DO NOTHING` Clause

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 INTO…的一种更短的语法替代方案。对冲突什么都不做。例如，以下语句是等价的：

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

`DO UPDATE` Clause (Upsert)

DO UPDATE子句会导致INSERT转换为冲突行上的UPDATE。后面的set表达式决定如何更新这些行。表达式可以使用特殊的虚拟表EXCLUDED，其中包含该行的冲突值。你还可以提供附加的WHERE子句，该子句可以从更新中排除某些行。不满足此条件的冲突将被忽略。

因为我们需要一种方法来引用要插入的元组和现有的元组，所以我们引入了特殊的exclude限定符。如果提供了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, 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 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;

`RETURNING` Clause

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

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

下面返回42，举例：

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

下一个示例展示了return子句更有用的情况。首先，用一个主键列创建一个表。然后创建一个序列，允许在插入新行时增加主键。当我们插入到表中时，我们还不知道序列生成的值，因此返回它们是有价值的。

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