提要:通过本教程,你将学到如何使用 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 以上可用。
我们新建一个 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
字段有唯一约束,用于确保客户的唯一性。
下面,往 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;