提要:通过本教程,你将学到如何使用 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_idnameemailactive。其中,name 字段有唯一约束,用于确保客户的唯一性。

upsert

下面,往 customers 表里插入几行:

INSERT INTO customers (NAME, email)
VALUES
 ('IBM', 'contact@ibm.com'),
 (
 'Microsoft',
 'contact@microsoft.com'
 ),
 (
 'Intel',
 'contact@intel.com'
 );
#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:由 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;

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

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

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

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

upsert

下一篇:PostgreSQL的DELETE