PostgreSQL Upsert插入更新

在本节中，我们将了解PostgreSQL upsert属性的工作原理，如果正在插入的行已经存在并且在insert on Conflict命令的帮助下出现在表中，则该属性用于插入或修改数据。

在 RDBMS（关系数据库管理系统）中，术语 upsert 被称为merge。当我们向特定表中插入新行时，如果该行已经存在，PostgreSQL 将对其进行升级，否则，它将添加新行。这个过程被称为 upsert，它是insert或update 命令的组合。

在PostgreSQL中，以下查询用于使用INSERT ON CONFLICT命令更新插入表：

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

为了支持 upsert 属性，PostgreSQL在 INSERT 命令中添加了ON CONFLICT 目标操作子句。

在上面的命令中，Conflict_target可以是以下之一：

目标	描述
column_name	它用于指定特定表中的列名。
ON CONSTRAINT constraint_ name	在此，约束名称可以是 UNIQUE 约束的名称。
WHERE predicate	带有谓词的 WHERE 子句。

在上述命令中，Conflict_action可以是以下之一： 

动作	描述
DO NOTHING	它定义如果该行已经存在于表中，我们什么都不做。
DO UPDATE SET column_1 = value_1, .. WHERE condition	此操作用于更新表中的某些字段。

PostgreSQL 更新插入功能示例

要实时了解 PostgreSQL Upsert 功能的工作原理，我们需要遵循以下过程：

首先，我们将借助以下 Create 命令创建一个名为customers的新表，如下所示：

CREATE TABLE officers (  
    officers_id serial PRIMARY KEY,  
    officers_name VARCHAR UNIQUE,  
    officers_email VARCHAR NOT NULL,  
    Officers_address VARCHAR NOT NULL  
);  

执行上述查询后，我们将获得以下消息窗口；Officers表已成功创建。

Officers表包含四列，例如officers_id、officers_name、officers_email 和 officers_address。

并且为了确保官员姓名的唯一性，我们对officers_name列使用唯一约束。

完成创建Officers表后，我们将使用 INSERT 命令向其中插入一些数据。

INSERT INTO officers   
VALUES   
(101,'Denny', 'denny11@hotmail.com','Newyork'),  
(102,'John', 'john23@gmail.com','Boston'),  
(103,'Olivia', 'olivia22@gmail.com','Newyork'),  
(104,'Mike', 'mike34@gmail.com','Newyork');  

执行上述查询后，我们将获得以下消息窗口；值已成功插入Officers表中。

让我们假设其中一位官员想要将他们的地址Newyork 更改为 Florida，因此我们可以在 UPDATE 命令的帮助下对其进行修改。

我们使用下面的INSERT ON CONFLICT命令来描述更新插入功能：

INSERT INTO officers (officers_name, officers_addrerss )  
VALUES('Olivia','Florida')  
ON CONFLICT ON CONSTRAINT officers_name  
DO NOTHING;  

上面的命令定义了如果officers_name出现在officers表中，则什么都不做或干脆忽略它。

下面的命令与上面的命令类似，但由于INSERT命令的目标，它使用officers_name列代替唯一约束 officers_name 。

INSERT INTO officers   
VALUES(102,'Olivia','olivia22@gmail.com','Florida')  
ON CONFLICT (officers_name)  
DO NOTHING;  

执行上述命令后，我们将得到以下消息窗口，其中显示INSERT 0 0，这意味着DO-NOTHING操作将返回INSERT 0 0冲突。因此，插入命令成功，没有插入任何行。

假设我们想在插入一个已经存在的官员时将新的 officers_address与旧的 officers_address结合起来。

在下面的命令中，我们使用 UPDATE 子句作为INSERT 命令的操作：

INSERT INTO officers   
VALUES(102,'Olivia','olivia22@gmail.com','Florida')  
ON CONFLICT (officers_name)  
DO UPDATE SET officers_address =   
EXCLUDED.officers_address || ',' || officers.officers_address;  

执行上述查询后，我们将得到以下消息窗口；这些值已成功更新到officers表中。

要检查上面的更新插入功能是否正常工作，我们将使用select 命令，如下面的命令所示：

Select * from officers;  

执行上述命令后，我们将得到以下输出：