如何使用 `INSERT ON CONFLICT` 在 PostgreSQL 中执行 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 选项允许您在发生冲突时有条件地更改现有记录，可以选择使用来自原始建议行的值。了解每种情况可能有用的场景以及学习如何使用这种通用格式可以帮助简化您在向现有数据集添加新数据时的查询。