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;  

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

热门文章

优秀文章