PostgreSQL 中的 Upsert(合并,重复更新时插入)

Bilal Shahid 2024年2月15日
  1. 什么是 PostgreSQL 中的 UPSERT
  2. 在 PostgreSQL 中使用 ON CONFLICT (PK_Name) DO UPDATE 操作进行 Upsert
  3. 在 PostgreSQL 中使用定制的触发器函数实现 Upsert
  4. 在 PostgreSQL 中使用顺序语句(不太推荐)实现 Upsert
  5. 在 PostgreSQL 中使用公用表表达式 (CTE) 实现 UPSERT
PostgreSQL 中的 Upsert(合并,重复更新时插入)

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

什么是 PostgreSQL 中的 UPSERT

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

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

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

假设我们有以下数据。

样本数据

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

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

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

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

使用 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 KEYDO UPDATE 操作?

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

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

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

我们将解释如何制作一个 FUNCTION,它会在调用 INSERTUPDATE 以实现 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');

现在将返回如下输出。

使用函数实现 upsert

在进行 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

BEGINCOMMITINSERT 语句括起来,以确保它现在是 TRANSACTION

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

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

在 PostgreSQL 中使用公用表表达式 (CTE) 实现 UPSERT

首先,什么是公用表表达式(CTE)?

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

查询可以写成如下。我们首先定义 WITH,它创建一个临时表,其中包含值 2, Toyota Supra5, 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 实现的可能方法。我们希望这对你有所帮助!

作者: Bilal Shahid
Bilal Shahid avatar Bilal Shahid avatar

Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!

GitHub

相关文章 - PostgreSQL Upsert