如何在 PostgreSQL 中使用 `INSERT ON CONFLICT` 进行数据 Upsert

简介

PostgreSQL 允许您根据记录是否已存在来在表中添加或修改记录。这通常被称为“upsert”操作（“insert”和“update”的合成词）。

PostgreSQL 中的实际实现使用带有特殊 ON CONFLICT 子句的 INSERT 命令来指定如果记录已存在于表中应执行的操作。您可以指定如果记录已存在于表中时是更新该记录还是静默跳过。

如何使用 INSERT...ON CONFLICT 构造

插入或更新操作的基本语法如下：

INSERT INTO my_table (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8)
ON CONFLICT <target> <action>;

在此上下文中，<target> 指定了您要为其定义策略的冲突。它可以是以下任何一种：

• 特定列的名称：(column1)
• 唯一约束的名称：ON CONSTRAINT <constraint_name>

伴随的 <action> 项将定义如果发生冲突，PostgreSQL 应执行的操作。指定的 <action> 可以是以下之一：

• DO NOTHING：告诉 PostgreSQL 保持冲突记录不变。本质上，此操作不进行任何更改，但会抑制尝试插入违反条件的行时通常会发生的错误。
• DO UPDATE：这告诉 PostgreSQL 您要更新表中已存在的行。更新的语法与正常的 UPDATE 命令的语法相同。

当指定 DO UPDATE 时，一个名为 EXCLUDED 的特殊虚拟表可用于 UPDATE 子句中。该表包含原始 INSERT 命令中建议的值（与现有表值冲突的值）。

注意：如果您使用 Prisma Client 连接到数据库，则可以使用专用的 upsert 操作执行 upsert 操作。

使用 DO NOTHING 操作

对于我们的示例，假设我们有一个名为 director 的表。

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    latest_film TEXT
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

让我们看看 PostgreSQL 通常如何处理拟议行与现有数据冲突的插入操作。假设已经有一个 id 为 3 的 director，PostgreSQL 将抛出错误

INSERT INTO director (id, name)
VALUES
    (3, 'susan'),
    (4, 'delores');

ERROR:  duplicate key value violates unique constraint "director_pkey"
DETAIL:  Key (id)=(3) already exists.

在这种情况下，即使只有第一条记录发生冲突，也没有添加任何拟议记录。如果我们想继续添加任何没有冲突的行，我们可以使用 ON CONFLICT DO NOTHING 子句。

在这里，我们告诉 PostgreSQL 如果发生冲突则继续，并继续处理其他行

INSERT INTO director (id, name)
VALUES
    (3, 'susan'),
    (4, 'delores')
ON CONFLICT (id) DO NOTHING;

INSERT 0 1

如果您查询该表，它将显示即使第一条记录与现有记录之一发生冲突，第二条记录仍被添加。

SELECT * FROM director;

 id |  name   | latest_film
----+---------+--------------
  3 | sue     |
  1 | frank   | second movie
  2 | bob     | fourth movie
  4 | delores |
(4 rows)

使用 DO UPDATE 操作

如果，我们想在行已存在于表中时更新它们，我们可以使用 ON CONFLICT DO UPDATE 子句。

这次，我们将执行与之前相同类型的查询，但这次，如果发生冲突，我们将更新现有记录

INSERT INTO director (id, name)
VALUES
    (2, 'robert'),
    (5, 'sheila'),
    (6, 'flora')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name;

INSERT 0 3

这次，我们指定了如果与我们提议的插入之一冲突，则对现有行进行的修改。我们使用虚拟 EXCLUDED 表（其中包含我们打算插入的项）在发生冲突时将 name 列更新为新值。

您可以通过输入以下内容来显示所有记录都已更新或添加

SELECT * FROM director;

 id |  name   | latest_film
----+---------+--------------
  3 | sue     |
  1 | frank   | second movie
  4 | delores |
  2 | robert  | fourth movie
  5 | sheila  |
  6 | flora   |
(6 rows)

结论

PostgreSQL 的 INSERT...ON CONFLICT 构造允许您在拟议记录与现有记录冲突时选择两种选项。 DO NOTHING 和 DO UPDATE 都各有其用途，具体取决于您要添加的数据与现有内容的关系。

DO NOTHING 选项允许您静默跳过冲突行，从而可以添加任何不冲突的其他记录。同时，DO UPDATE 选项允许您在发生冲突时有条件地修改现有记录，并可选择使用原始拟议行中的值。了解每种情况可能有用的场景并学习如何使用这种通用格式可以帮助您在向现有数据集添加新数据时简化查询。