迹忆客 专注技术分享

本文描述了如何在 PostgreSQL 中执行 upsert 操作。

什么是 PostgreSQL 中的 UPSERT

UPSERT 是在数据库查询中结合 UPDATE 和 INSERT 操作创造的一个术语。它不是我们最常讨论的两个 DBMS 中的关键字 MySQL 或 PostgreSQL。

因此，我们不能使用 UPSERT，无论是从句还是运算符。但是，它可能作为适当的子句存在于其他数据库系统中。

UPSERT 很简单；如果你想将数据插入表中，它将更新旧数据集，并且不知何故，该数据之前已经存在（重复）。如果之前不存在，它将插入数据集而不进行修改。

假设我们有以下数据。

现在我们要插入以下语句。

Insert into car values(2, 'Toyota Vigo')

INSERT 将返回重复错误，因为键已经存在。

但是，如果我们使用 UPSERT 方法执行此操作，数据将被更新，结果将是：

请记住，没有特定的 UPSERT 方法，只有一个术语，用于表示能够执行此类操作的一组操作。

在 MySQL 中，你可以使用以下语句实现 UPSERT。

INSERT INTO car values (2, 'Toyota Corolla') ON DUPLICATE KEY UPDATE name = 'Toyota Vigo';

这将检测表中的 DUPLICATE KEY -> 2，然后调用 UPDATE 方法来更新该行。

现在让我们继续学习如何在 PostgreSQL 中实现它。

在 PostgreSQL 中使用 ON CONFLICT (PK_Name) DO UPDATE 操作进行 Upsert

让我们使用我们上面制作的相同的 CAR 表。假设我们想在 PostgreSQL 的表中插入一些数据集。

insert into car values(2, 'Toyota Vigo')
ON CONFLICT (id) DO UPDATE
							SET id = excluded.id,
								name = excluded.name
;

你是否看到 ON CONFLICT 后跟 PRIMARY KEY 和 DO UPDATE 操作？

好吧，它检查 PRIMARY KEY，在我们的例子中是 ID，如果它找到 CONFLICT，它将 UPDATE 而不是抛出错误。你将在查询中看到术语 EXCLUDED。

EXCLUDED 是一个表，其中包含建议在 PostgreSQL 中插入的行。因此，当你碰巧为新数据集调用 INSERT 时，你的数据最终会附加到 EXCLUDED 表中。

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

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

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

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

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

select * from upsert_imp(2, 'Toyota Supra');

现在将返回如下输出。

在进行 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 中使用公用表表达式 (CTE) 实现 UPSERT

首先，什么是公用表表达式（CTE）？

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

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

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

WITH new_values (id, name) as (
  values
     (2, 'Toyota Supra'),
	 (5, 'Honda City')

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

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

调用上述内容：

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

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

这些是在 PostgreSQL 中创建 UPSERT 实现的可能方法。我们希望这对你有所帮助！