PostGreSQL ON DUPLICATE KEY UPDATE

小石头

发布于 2023-03-24 10:46:33

3K0

发布于 2023-03-24 10:46:33

文章被收录于专栏：小石头小石头

PGsql 也有类似于mysql ON DUPLICATE KEY UPDATE 的语法注意，ON CONFLICT 只在 PostgreSQL 9.5 以上可用。
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：当记录存在时，更新表中的一些字段

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', '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

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-3-22 1，如有侵权请联系 cloudcommunity@tencent.com 删除

postgresql