PostgreSQL / 插入和修改数据

如何在 PostgreSQL 中使用 `INSERT ON CONFLICT` 来 upsert 数据

内容

简介
如何使用 INSERT...ON CONFLICT 结构
使用 DO NOTHING 操作
使用 DO UPDATE 操作
结论

分享到

简介

PostgreSQL 允许您根据记录是否存在于表中，在表中添加或修改记录。这通常被称为 "upsert 操作"（“插入”和“更新”的合成词）。

PostgreSQL 中的实际实现使用 INSERT 命令，并带有一个特殊的 ON CONFLICT 子句来指定如果记录已存在于表中，该怎么做。您可以指定如果记录已存在于表中，是否希望更新记录或将其静默跳过。

如何使用 `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 的导演，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)