PostgreSQL / 插入和修改数据

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

内容

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

分享到

简介

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

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)