PostgreSQL的UPSERT简介及实例

迪鲁宾
923 阅读4分钟

PostgreSQL UPSERT

浅谈PostgreSQL的UPSERT

UPSERT一词在关系型数据库中被称为合并。当你试图在表中插入一条新的记录时，那么PostgreSQL将检查表中是否已经存在相同的记录。如果表中已经存在类似的记录，PostgreSQL将更新现有的记录；否则，它将插入该记录作为一个新条目。正如我们所看到的，UPSERT操作要么更新记录，要么将记录插入到表中，由于这个原因，我们把这个术语称为UPSERT（无论是UPDATE还是INSERT）。在这个主题中，我们将学习PostgreSQL的UPSERT。

语法

我们可以使用下面的 INSERT ON CONFLICT 语句作为语法，以便在 PostgreSQL 中使用 UPSERT 操作。

INSERT INTO table(columns) VALUES(values)
ON CONFLICT target action;

解释

为了支持UPSERT操作，PostgreSQL增加了ON CONFLICT目标动作。

  • 列。定义列的名称。
  • ON CONSTRAINT constraint_name： 定义了约束的名称，可以是UNIQUE约束。
  • WHERE条件。定义了带有条件的WHERE子句
  • 行动。 DO NOTHING：定义了如果一个表已经包含了什么都不做。
  • DO UPDATE SET column_name_1 = value_field_1, ... WHERE predicate :定 义了我们可以更新表中的一些字段。

UPSERT在PostgreSQL中如何工作？

  1. UPSERT结合了两个DML，第一个是UPDATE，第二个是INSERT。
  2. 如果我们比较IF EXISTS和UPSERT来理解UPSERT的工作，那么对于每一个更新操作，UPSERT都会从表中删除一个额外的读数。但是在INSERT操作的情况下，UPSERT和IF EXISTS操作对表的读操作数量相同。
  3. 为了检查一个记录是否已经存在于表中，PostgreSQL的UPSERT语句使用记录的唯一标识符作为键来匹配表中的记录。

在PostgreSQL中实现UPSERT的例子

让我们创建一个名为 "公司 "的表，以了解PostgreSQL的UPSERT功能。

我们将使用下面的CREATE TABLE语句来创建一个公司表。

CREATE TABLE company (
comp_id serial PRIMARY KEY,
comp_name VARCHAR UNIQUE,
comp_email VARCHAR NOT NULL,
comp_contact VARCHAR
);

在这里你可以看到，表company包含四个列，分别是comp_id, comp_name, comp_email, 和comp_contact。此外，我们在公司表的comp_name列上添加了UNIQUE约束，以确保公司的唯一性。

现在我们将通过INSERT INTO语句向表中插入一些记录，如下所示。

INSERT INTO company (comp_name, comp_email)
VALUES
('Bloomberg', 'contact@bloomberg.com'),
('Facebook', 'contact@fb.com'   ),
('Google', 'contact@google.com'   );

通过使用下面的快照和SELECT语句来说明上述INSERT语句的结果。

select * from company;

PostgreSQL Upsert output 1

现在我们可以用UPDATE语句更新任何一条记录的email id信息。考虑一个例子，Facebook将其电子邮件ID从contact@fb.com 改为contact@facebook.com。

我们将使用以下INSERT ON CONFLICT语句来演示UPSERT功能。

INSERT INTO company (comp_name, comp_email) VALUES ( 'Facebook', 'contact@facebook.com' ) ON CONFLICT ON CONSTRAINT company_comp_name_key DO NOTHING;

上述语句定义，如果公司名称存在于公司表中，则什么也不做，只是忽略掉。

通过使用下面的快照和SELECT语句来说明上述INSERT语句的结果。

select * from company;

PostgreSQL Upsert output 2

下面的语句与上面的语句相似，但它将使用公司表中的comp_name列而不是唯一约束comp_name作为INSERT语句的目标。

INSERT INTO company (comp_name, comp_email)
VALUES
(
'Facebook',
'contact@facebook.com'
)
ON CONFLICT ON CONSTRAINT company_comp_name_key
DO NOTHING;

现在，如果我们想在插入已经存在的公司表时，将新公司的电子邮件与旧公司的电子邮件连接起来。

我们可以使用UPDATE子句作为INSERT INTO语句的动作，定义如下。

INSERT INTO company (comp_name, comp_email)
VALUES
(
'Facebook',
'contact@facebook.com'
)
ON CONFLICT (comp_name)
DO UPDATE
SET comp_email = EXCLUDED.comp_email || ';' || customers.comp_email;

通过使用下面的快照和SELECT语句来说明上述INSERT语句的结果。

select * from company;

PostgreSQL Upsert output 3

在PostgreSQL中使用UPSERT的优点

  • PostgreSQL的UPSERT只不过是将两个数据处理操作结合在一起，成为一个更新操作和一个插入操作。
  • 如果我们使用PostgreSQL的UPSERT功能，那么对于每一次更新，我们实际上从表中删除了一个额外的读。
  • 我们可以通过使用UPSERT操作更新记录来避免对数据库进行不必要的读操作。
  • 通过使用UPSERT操作，我们可以避免重复键的违反，因为它将在表中更新或插入一条记录。

总结

我们希望从上面的文章中你已经了解了如何使用PostgreSQL UPSERT以及PostgreSQL UPSERT是如何工作的。此外，我们还添加了一些PostgreSQL UPSERT的例子来详细了解它。

avatar
文章
阅读
粉丝