postgresql无则插入,有则更新问题

目录
  • 准备工作
  • postgresql 9.5版本之前实现upsert功能
  • postgresql 9.5版本之后实现upsert功能
  • 总结

准备工作

我们只需要准备一张表

upsert是一个组合词，即当往表中插入记录，如果该记录已存在则更新，否则插入新记录。

postgresql 9.5版本之前实现upsert功能

语法

postgresql 9.5版本之前想实现upsert功能可以使用with语法来实现。

实例

SQL如下

WITH UPSERT
         AS ( UPDATE org SET org_name = '复仇者联盟2', modify_time = now() WHERE org_id = 2 RETURNING *)
INSERT
INTO org (org_id, org_no, org_name)
SELECT 2,
       '002',
       '复仇者联盟'
WHERE NOT EXISTS(SELECT 1 FROM upsert WHERE org_id = 2);

第一次执行SQL，表中没有org_id为2的数据，则插入一条新数据。

执行成功了，刷新一下表，可以看到插入了一条新数据。

再次执行SQL，这时表中已经有一条为org_id为2的数据了，那么则更新数据。

刷新表，可以看到更新了org_name和modify_time字段。

嘚吧嘚(SQL语法简解)

那么是如何做到呢？

  • 首先执行update语句，如果找到满足条件的数据就更新，并把该数据存放到upsert中，在执行insert时就不满足条件了；
  • 如果找不到满足条件的数据，upsert中就是空的，在执行insert语句时就满足条件了。二者是互斥的。

postgresql 9.5版本之后实现upsert功能

语法

postgresql 9.5版本就可以使用INSERT ON CONFLICT语句了，语法如下：

insert into table_name(column_list)
values(value_list)
on conflict target action;

实例

SQL如下：

insert into org(org_id, org_no, org_name)
values (3, '003', '银河护卫队')
on conflict(org_id) do update set org_name='银河护卫队2',
                                  modify_time=now();

第一次执行SQL，表中没有org_id为3的数据，则插入一条新数据。

执行成功了，刷新一下表，可以看到插入了一条新数据。

第二次执行SQL，这时表中已经有一条为org_id为3的数据了，那么则更新数据。

可以看到更新了org_name和modify_time字段。

嘚吧嘚(SQL语法简解)

其实大体思路和之前还是一样，就是9.5版本有了INSERT ON CONFLICT语句之后，使得书写SQL更加简单了，那就简单解释一下SQL语法中的两个名词吧。

  • target可以理解为目标或者限制条件，一般是某个字段名。
  • action是要做的具体操作，比如update、delete、do nothing等。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。

赞 (0)

相关推荐

  • PostgreSQL实现批量插入、更新与合并操作的方法

    PostgreSQL实现批量插入、更新与合并操作的方法

    前言 就在 2019 年 1 月份微软收购了 PostgreSQL 数据库的初创公司 CitusData, 在云数据库方面可以增强与 AWS 的竟争.AWS 的 RDS 两大开源数据库就是 MySQL(Aurora 和 MariaDB 是它的变种) 和 PostgreSQL. 而 PostgreSQL 跳出了普通关系型数据库的类型约束,它灵活的支持 JSON, JSONB, XML, 数组等类型.比如说字段类型可以是各种形式的数组,一维或多维. create table t1( address

  • PostgreSQL upsert(插入更新)数据的操作详解

    本文介绍如何使用PostgreSQL upsert特性插入或当被插入数据已存在则更新数据. 1. 介绍PostgreSQL upsert 在关系型数据库中,upsert是一个组合词,即当往表中插入记录,如果该记录已存在则更新,否则插入新记录.为了使用该特性需要使用INSERT ON CONFLICT语句: INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT target action; 该语法中target可以是下

  • Postgresql通过查询进行更新的操作

    我就废话不多说了,大家还是直接看实例吧~ UPDATE tb1 SET c1=b.c1 , c2=b.c2 FROM b WHERE tb1.c3 = b.c3 AND tb1.c4 = b.c4 补充:postgresql数据库 如果存在则更新(update),如果不存在则插入(insert) 格式: insert into ...... on conflict(column_name) do ...... 例子: uxdb=# create table tbl_c (id int uniqu

  • postgresql无则插入,有则更新问题

    目录 准备工作 postgresql 9.5版本之前实现upsert功能 postgresql 9.5版本之后实现upsert功能 总结 准备工作 我们只需要准备一张表 upsert是一个组合词,即当往表中插入记录,如果该记录已存在则更新,否则插入新记录. postgresql 9.5版本之前实现upsert功能 语法 postgresql 9.5版本之前想实现upsert功能可以使用with语法来实现. 实例 SQL如下 WITH UPSERT AS ( UPDATE org SET org_

  • PostgreSQL数据库事务插入删除及更新操作示例

    PostgreSQL数据库事务插入删除及更新操作示例

    目录 INSERT DELETE UPDATE 事务 INSERT 使用INSERT语句可以向表中插入数据. 创建一个表: CREATE TABLE ProductIns (product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER DEFAULT 0, purchase_price INTEGER , regist_d

  • MySql 如何实现无则插入有则更新

    摘要 某些场景会有这样的需求:无记录则插入,有记录则更新.例如:新增用户,以身份证号码作为唯一身份标识,插入时若先查询是否存在记录再决定插入还是更新,在高并发情况下必然存在问题.本文提供三种解决方案. 方案一 加锁 无论通过synchronized锁.ReentranLock锁还是分布式锁,都可以解决该问题.缺点是,加锁会影响性能.方法二和三都是数据库层面解决方案,个人感觉比方法一好一些. 方案二 Unique和Replace Into - SELECT - 首先对唯一性的字段添加唯一索引ALT

  • spring data jpa开启批量插入、批量更新的问题解析

    spring data jpa开启批量插入、批量更新的问题解析

    最近准备上spring全家桶写一下个人项目,该学的都学学,其中ORM框架,最早我用的是jdbcTemplate,后来用了Mybatis,唯独没有用过JPA(Hibernate)系的,过去觉得Hibernate太重量级了,后来随着springboot和spring data jpa出来之后,让我觉得好像还不错,再加上谷歌趋势... 只有中日韩在大规模用Mybatis(我严重怀疑是中国的外包),所以就很奇怪,虽然说中国的IT技术在慢慢抬头了,但是这社会IT发展的主导目前看来还是美国.欧洲,这里JPA

  • 使用Postgresql 实现快速插入测试数据

    使用Postgresql 实现快速插入测试数据

    1.创建常规的企业信息表 create table t_centerprises( objectid bigint not null, /*唯一编号(6位行政区号+6位sn)*/ divid uuid not null, /*行政区唯一代码*/ name text not null, /*企业名称*/ address text not null, /*企业地址*/ post text, /*企业邮编*/ contacts text, /*联系人*/ tel text, /*联系电话*/ fax

  • Mysql如何实现不存在则插入,存在则更新

    Mysql如何实现不存在则插入,存在则更新

    目录 数据准备 ON DUPLICATE KEY UPDATE REPLACE INTO … VALUES… INSERT IGNORE INTO … VALUES… 使用DUAL虚表和NOT EXISTS 数据准备 ON DUPLICATE KEY UPDATE insert into test_table(id,username) VALUES(4,'fukaiit') ON DUPLICATE KEY UPDATE username='fukaiit' 执行如上sql,该id不存在(此处i

  • 判断触发器正在处理的是插入，删除还是更新触发

    但是有时候,可以视看处进逻辑程度,可以把三者写成一个触发器,只是在其中稍作判断而已. 你可以根据从下面方法判断触发器是是处理了插入,删除还是更新触发的: 复制代码 代码如下: --宣告两个变量 DECLARE @D BIT = 0 DECLARE @I BIT = 0 --如果在DELETED内部临时触发表找到记录,说明旧数据被删除 IF EXISTS(SELECT TOP 1 1 FROM DELETED) SET @D = 1 --如果在INSERTED内部临时触发表找到记录,说明有新数据插

  • mysql 数据插入和更新及删除详情

    mysql 数据插入和更新及删除详情

    目录 1.插入 2.更新 3.删除 1.插入 INSERT INTO customers( customers.cust_address, customers.cust_city, customers.cust_state, customers.cust_zip, customers.cust_country, customers.cust_contact, customers.cust_email ) VALUES('zhangsan','good','111','ca','dasdsa','

随机推荐