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

摘要：在本教程中，您将学习如何使用 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_id、name、email和active。name列具有唯一约束，以保证客户名称的唯一性。

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

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

假设微软将联系邮箱从"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：

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