PostgreSQL / 插入和修改数据

如何使用 `INSERT ON CONFLICT` 在 PostgreSQL 中执行 upsert 操作

内容

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

分享到

简介

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)