PostgreSQL的ON CONFLICT-upsert-PostgreSQL轻松学-SJK66.COM

提要：通过本教程，你将学到如何使用 PostgreSQL 的 upsert 功能：当记录不存在时，执行插入；否则，进行更新。

PostgreSQL 的 upsert 简介

在关系数据库中，术语 upsert 被称为合并(merge)。意思是，当执行 INSERT 操作时，如果数据表中不存在对应的记录，PostgreSQL 执行插入操作；如果数据表中存在对应的记录，则执行更新操作。这就是为什么将其称为 upsert（update or insert）的原因。

通过 INSERT ON CONFLICT 来使用 upsert 功能：

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

target 可以是：

(column_name)：一个字段名
ON CONSTRAINT constraint_name：其中的 constraint_name 可以是一个唯一约束的名字
WHERE predicate：带谓语的 WHERE 子句

action 可以是：

DO NOTHING：当记录存在时，什么都不做
DO UPDATE SET column_1 = value_1, .. WHERE condition：当记录存在时，更新表中的一些字段

注意，ON CONFLICT 只在 PostgreSQL 9.5 以上可用。

PostgreSQL 的 upsert 示例

我们新建一个 customers 表来进行演示：

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

customers 表有4个字段：customer_id、name、email 和 active。其中，name 字段有唯一约束，用于确保客户的唯一性。

upsert

下面，往 customers 表里插入几行：

INSERT INTO customers (NAME, email)
VALUES
 ('IBM', '[email protected]'),
 (
 'Microsoft',
 '[email protected]'
 ),
 (
 'Intel',
 '[email protected]'
 );

#SELECT * FROM customers;
 customer_id |   name    |         email         | active
-------------+-----------+-----------------------+--------
           1 | IBM       | contact@ibm.com       | t
           2 | Microsoft | contact@microsoft.com | t
           3 | Intel     | contact@intel.com     | t
(3 rows)

假设 Microsoft 更换了联系方式 email：由 [email protected] 变成了 [email protected]，我们可以使用 UPDATE 语句进行修改。然而，为了演示 upsert 功能，我们使用 INSERT ON CONFLICT 语句：

INSERT INTO customers (NAME, email)
VALUES
 (
 'Microsoft',
 '[email protected]'
 ) 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

这个语句指明了，当数据存在时，什么都不做(DO NOTING)。下面的语句有一样的效果，区别在于使用的是 name 字段，而不是约束的名字：

INSERT INTO customers (name, email)
VALUES
 (
 'Microsoft',
 '[email protected]'
 ) 
ON CONFLICT (name) 
DO NOTHING;

我们的目标是修改客户的 email，所以应该用这条语句：

INSERT INTO customers (name, email)
VALUES
 (
 'Microsoft',
 '[email protected]'
 ) 
ON CONFLICT (name) 
DO
 UPDATE
   SET email = EXCLUDED.email;

upsert

快速入门

什么是PostgreSQL 安装PostgreSQL 连接到数据库下载PostgreSQL示例数据库导入示例数据探索服务器与数据库对象

管理表结构

PostgreSQL的数据类型创建PostgreSQL数据表修改PostgreSQL数据表删除PostgreSQL数据表清空PostgreSQL数据表 PostgreSQL的CHECK约束 PostgreSQL的非空约束 PostgreSQL的外键 PostgreSQL的主键 PostgreSQL的唯一约束

PostgreSQL的ON CONFLICT upsert

PostgreSQL 的 upsert 简介

PostgreSQL 的 upsert 示例

快速入门

基础知识

管理表结构