PostgreSQL 教程: 使用 INSERT ON CONFLICT 语句进行更新插入

八月 29, 2023

摘要:在本教程中,您将学习如何使用 PostgreSQL upsert 功能来插入或更新数据(如果要插入的行已存在于表中)。

PostgreSQL upsert 简介

在关系数据库中,术语 upsert 称为合并。这个想法是,当您向表中插入新行时,如果该行已存在,PostgreSQL 将更新该行,否则,它将插入新行。这就是为什么我们称该操作为 upsert(更新或插入的组合)。

要在 PostgreSQL 中使用 upsert 功能,请使用INSERT ON CONFLICT语句,如下:

INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

PostgreSQL 在INSERT语句中添加了ON CONFLICT target action子句以支持 upsert 功能。

在此语句中,target可以是以下之一:

  • (column_name) – 列名称。
  • ON CONSTRAINT constraint_name – 其中约束名称可以是 UNIQUE 约束的名称。
  • WHERE predicate –带有谓词的WHERE 子句

action可以是以下之一:

  • DO NOTHING – 表示如果该行已存在于表中,则不执行任何操作。
  • DO UPDATE SET column_1 = value_1, .. WHERE condition – 更新表中的一些字段。

请注意,ON CONFLICT子句仅在 PostgreSQL 9.5 和以上版本可用。如果您使用的是早期版本,则需要一种解决方法才能拥有更新插入功能。

如果你也在使用 MySQL,你会发现 upsert 功能与 MySQL 中的insert on duplicate key update语句类似。

PostgreSQL 更新插入示例

以下语句创建一个新表,名为customers,来演示 PostgreSQL upsert 功能。

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
	customer_id serial PRIMARY KEY,
	name VARCHAR UNIQUE,
	email VARCHAR NOT NULL,
	active bool NOT NULL DEFAULT TRUE
);

customers表由四列组成:customer_idnameemailactivename列具有唯一约束,以保证客户名称的唯一性。

以下INSERT语句将一些行插入到customers表中。

INSERT INTO 
    customers (name, email)
VALUES 
    ('IBM', 'contact@ibm.com'),
    ('Microsoft', 'contact@microsoft.com'),
    ('Intel', 'contact@intel.com');

img

假设微软将联系邮箱从"contact@microsoft.com"更改为"hotline@microsoft.com",我们可以使用 UPDATE 语句进行更新。但是,为了演示 upsert 功能,我们使用以下INSERT ON CONFLICT语句:

INSERT INTO customers (NAME, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

该语句指定如果客户名称存在于customers表中,则忽略它(不执行任何操作)。

以下语句与上面的语句等效,但它使用name列而不是唯一约束名称作为INSERT语句的目标。

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO NOTHING;

假设,您想在插入已存在的客户时将新电子邮件与旧电子邮件连接起来,在这种情况下,您使用UPDATE子句作为INSERT语句的操作,如下所示:

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO 
   UPDATE SET email = EXCLUDED.email || ';' || customers.email;

输出:

INSERT 0 1

以下语句验证 upsert:

img

在本教程中,您了解了使用INSERT ON CONFLICT语句的 PostgreSQL upsert 功能。