迹忆客 专注技术分享

本文将介绍 PostgreSQL 中的 INSERT ON DUPLICATE UPDATE。插入重复更新 是什么意思？为什么要使用它？

每当你将插入记录到表中时，都会添加具有唯一主键的数据集。它可以是每个数据集不同的 ID 或自动生成的数字。

假设你插入一条带有主键的记录，该记录与已经存在的具有该主键的数据集冲突。

在这种情况下，你有两个选择。使用你的新数据集更新该行或保留更新并保留原始数据。

你倾向于选择什么取决于你希望如何对数据进行排序并将其保存在数据库中。

PostgreSQL 中的 ON CONFLICT 子句

Upsert 是 UPDATE ON INSERT VIOLATION 的缩写，可以在 PostgreSQL 中的以下查询中实现。首先，假设我们为 CAR 创建一个表并插入一行。

create table car(
	id int PRIMARY KEY,
	owner TEXT
);

insert into car values (1, 'John');

现在让我们继续尝试在该表中插入一个值 (1, 'Mark')。你认为会发生什么？

将发生的第一件事是会出现如下所示的 VIOLATION 错误。

输出：

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

现在，你明白为什么会发生主键违规了吗？因为 Mark 也有已经存在于 John 的键 1。

为避免这种情况，我们将使用 ON CONFLICT 子句。

insert into car values (1, 'Mark')
on conflict (id) do update
set id = excluded.id,
	owner = excluded.owner;

现在，每当我们的查询发现冲突时，它都会更新表中存在的该冲突主键的行，并将预先存在的数据的 ID 和所有者设置为这些新键。

但是，那个 EXCLUDED 表到底是什么？EXCLUDED 表表示建议插入的行，如 PostgreSQL 文档所指定。

因此，我们使用 EXCLUDED 表来获取从 INSERT 查询推送的行，然后使用它们。

假设你不想对违规做任何事情并继续保留原件。你最好使用这种语法。

insert into car values (1, 'Mark')
on conflict (id) do nothing;

DO NOTHING 忽略任何可能的更改。

要在 psql 中执行这些相同的查询，请使用上述语句，但要大写 KEYWORDS。永远不要把 INSERT 写成 insert，否则你会遇到错误。

在 PostgreSQL 中使用定制的触发器或函数实现 Upsert

在这里，我们将解释如何制作一个 FUNCTION，它会在调用 INSERT 或 UPDATE 以实现 UPSERT 时触发。同样，你可以通过将返回类型更改为 TRIGGER 来创建 TRIGGER。

create or replace function upsert_imp (idt int, ownert TEXT) returns void as
$$
	Begin
		loop
			update car set owner = ownert where id = idt;
		if found then
			return;
		end if;
		begin
			insert into car values(ownert, idt);
			return;
		end;
		end loop;
	end;
$$ language plpgsql;

该函数非常简单，运行一个循环来检查每一行的 ID；如果它与给定的参数匹配，则返回 UPDATE 之后的表；否则，它是 INSERTS。

你可以像这样调用上面的。

select * from upsert_imp(1, 'Mark');

在进行 TRIGGER 时，请确保将 LOOP 替换为 FOR 或有效的 IF 检查，以免它无限期地旋转而违反条件。

在 PostgreSQL 中使用顺序语句（不太推荐）实现 Upsert

你可以使用 UPDATE 调用，但将其与以下 INSERT 语句结合使用。

insert into car values(4, 'Toyota Supra') on conflict do nothing;

你甚至可以使用 NOT EXISTS 子句，但这个简单的语句可以正常工作。如果没有重复，它将插入该行或完全跳过。

insert into car select 2, 'Toyota Supra' where not exists (Select 1 from CAR where id=2);

但是，有一个条件。在许多系统中，可能存在 RACE 条件。

如果有人在你 INSERT 一行时 DELETES 一行，你的行将丢失。为此，你可以使用 TRANSACTION。

用 BEGIN 和 COMMIT 将 INSERT 语句括起来，以确保它现在是 TRANSACTION。

begin;
insert into car select 2, 'Toyota Supra' where not exists (Select 1 from CAR where id=2);
commit;

但是，PostgreSQL 已经在每个语句中添加了隐式的 BEGIN 和 COMMIT，因此不需要显式声明。

在 PostgreSQL 中 COMMON TABLE EXPRESSIONS (CTE) 实现 UPSERT

首先，什么是 COMMON TABLE EXPRESSIONS？

CTE 用作 QUERY 中的临时表，用于存储以后可能使用的值。它的行为类似于 TRIGGER 中使用的 NEW 表。

查询可以写成如下。我们首先定义 WITH，它创建一个临时表，其中包含值 2, Jackson 和 5, Maria。

然后这些值被传递到 UPSERT 查询中，它从 NEW_VALUES 表中创建 NV，如果它们已经存在于 CAR 表中，它会相应地更新这些值。

WITH new_values (id, owner) as (
  values
     (2, 'Jackson'),
	 (5, 'Maria')

),
upsert as
(
    update car
        set id = nv.id,
            owner = nv.owner
    FROM new_values nv
    WHERE car.id = nv.id
    RETURNING car.*
)

这将返回一个 CAR.*，表示 CAR 表中的所有行。

调用上述内容：

INSERT INTO car (id, owner)
SELECT id, owner
FROM new_values
WHERE NOT EXISTS (SELECT 1
                  FROM upsert up
                  WHERE up.id = new_values.id);

在这里，我们检查在返回 CAR.*时 UPSERT 中创建的表是否已经包含该值；如果没有，INSERT INTO 有效。但是，如果是这样，UPSERT 已经在内部处理了修改。

关于 PostgreSQL 中 ON CONFLICT DO UPDATE 的注释

与每种解决方案一样，一些问题通常会干扰性能或空间或编码效率。

在我们的查询中，每当我们调用 ON CONFLICT DO UPDATE 时，我们可以假设在此之后，在我们的系统中，可以有多个用户同时访问数据库并发出命令。

因此，如果 USER 1 以 USER 2 的身份同时发出 INSERT DO UPDATE，则会再次出现 VIOLATION 错误。为什么？

因为当 USER 2 调用 INSERT 时，它并不知道当时该行是否存在；因此它发生了冲突。两个用户同时发出查询，数据库同时运行它们。

这类问题被称为 RACE CONDITION，如果你愿意，你可以稍后阅读它们。

随着 RACE CONDITION 的出现，解决这个问题的方法也不同。人们争辩说交易可能是一个可能的答案。

但是即使 TRANSACTION 保证将查询分开，它也不能确保查询与 VIOLATION 是安全的。为什么？

如果 USER 1``TRANSACTS 是它的查询，你不能保证一旦 TRANSACTION 运行，它会锁定查询并且不会让其他查询继续进行。因为 TRANSACTIONS 只会阻止更改显示给其他用户，并且只能选择放弃并回滚。

即使你使用声称按顺序运行 QUERIES 的 SERIAL TRANSACTIONS，也可能出现如下错误。

commit failed: ERROR:  could not serialize access due to read/write dependencies among transactions

因此，你最好使用 ADVISORY LOCKS 和 SAVE POINTS。ADVISORY LOCKS 倾向于锁定，防止你的查询干扰和有效工作。

即使他们可能会重现错误，但这样做的倾向仍然很低。

另一方面，SAVE POINTS 包含在 TRANSACTIONS 中。每当你想在 TRANSACTION 和回滚期间放弃更改时，你可以使用这些 SAVEPOINTS 恢复你的更改。

因此，如果 INSERT 失败，例如，你只是没有跳过错误，你现在可以回滚到 SAVE POINT，执行 UPDATE，然后照常工作。

我们希望你现在已经清楚 UPSERT 的基础知识，并深入了解它的工作原理和可能的 CONSTRAINTS。

我们的团队有效地工作，在知识和高效的编码算法方面为你提供两全其美的体验。我们相信，你越了解事物的角落和缝隙，你在提供解决方案和解决这些问题时就越有效。