踩坑 pgsql 插入或者更新

原创

已于 2022-05-07 11:01:53 修改 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #database

于 2022-05-07 11:00:03 首次发布

本文介绍了在pgsql数据库中，如何利用唯一索引来实现数据的智能插入或更新操作，对比了与mysql不同的处理方式，强调了pgsql需要唯一索引的要求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

踩坑 pgsql 根据唯一索引进行插入或者更新

pgsql写法，需要唯一索引

PGSQL数据库中根据唯一索引判断存在不存在，存在则更新，不存在就新增

    <update id="insertRecommendation" parameterType="com.entity.WarningRecommendation" >
        INSERT INTO RECOMMENDATION(METRIC_ID, RECOMMENDATION)
        VALUES(#{
   metricId}

最低0.47元/天解锁文章

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

神秘404

关注关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

MySQL + PostgreSQL批量插入更新insertOrUpdate

学Java，找哪吒

11-15

9497

???? 周周有福利，周周有惊喜哪吒社区 - 风火轮计划 ???? Java学习路线配套文章：Java学习路线总结，搬砖工逆袭Java架构师（全网最强） ???? Java经典面试题大全：10万字208道Java经典面试题总结(附答案) ???? 简介：Java领域优质创作者????、优快云哪吒公众号作者✌ 、Java架构师奋斗者???? ???? 扫描主页左侧二维码，加入群聊，一起学习、一起进步 ???? 欢迎点赞 ???? 收藏 ⭐留言 ???? 这里写目录标题一、百度百科1、MySQL2、Pos

【PG备份恢复】基于时间点的恢复（踩坑指南）

DBA之路的博客

09-25

1227

因为备份的时候加了-R 所以会创建 standby.signal 文件，如果你是想让改实例作为主库启动将该文件删除。假如以下场景发生：有研发删除了foo全表，或者错误更新了foo全表，或者机房故障导致数据库启动不了。查看归档目录，可以看到 0000000A0000000000000020 日志已经归档。如果只用全备恢复，从全备时刻到故障时刻的增量的DML 就会丢失。再次查看当前的WAL日志，可以看到由原来的20 变为了 21。不再使用，如果该文件存在，服务器将无法启动。

1 条评论您还未登录，请先登录后发表或查看评论

Postgresql插入或更新操作upsert

dieruowan6112的博客

01-10

764

幂等性的一个要求是多次操作的结果一致。对于update操作，多次直接的结果都是最后update的值，是满足需求的。但对于insert，如果已经插入，第二次会报错，duplicate error，主键重复或者unique key duplicate。所以需要做一下处理。最简单的就是，try-catch，当报错的时候，调用update去更新，或者策略更简单点，直接返回就行，...

PgSQL实现更新或插入

qq_43429919的博客

11-24

1873

PgSQL实现更新或插入 insert into tableA ( C_BH, C_NAME ) values ('001','名称') ON conflict(C_BH) do update set C_NAME = '名称' conflict里需要放唯一约束，不然会报错。 PostgreSQL的conflict语法只在PostgreSQL-9.5以上才可生效，9.5以下版本直接报错 ...

PostgreSQL踩坑现场

WOLF

02-20

354

PostgreSQL表名、字段名、别名等大小敏感，默认都会转化成小写形式。如果名字中有大写字母，必须分别添加双引号。在写后台时，注意添加如表名：TestTable中有个字段名userName 写sql查询时： select “TestTable”.“userName” from “TestTable”; 后台拼装sql语句时: String sql = “select “TestTable”.“...

pgsql执行insertOrUpdate操作，执行时，表中没有此记录执行新增，有此记录执行编辑

weixin_43230825的博客

05-30

1442

ON conflict ( ) DO 子句实现示例表：tb_user_info CREATE TABLE "public"."tb_user_info" ( "user_id" int4 NOT NULL, "sex" varchar(255) COLLATE "pg_catalog"."default", "password" varchar(255) COLLATE "pg_catalog"."default" ) 使用ON conflict ( ) DO子句，下面的意思...

ASP.NET Core 中使用 EF Core For PGSQL

m0_46202128的博客

10-01

3520

.NET Core 中使用 EF Core For PGSQL 开发环境：系统：win10 家庭普通版开发工具：VS2019 社区版目标框架：DotNet Core 3.1 前言本文介绍在.NET Core项目中使用EF Core（ORM框架又叫对象和关系的映射器）针对于PG（PostgreSQL）数据库的增删查改的操作。 1准备项目在vs中创建一个ASP.NET Core的项目，并NuGet程序包管理器中安装以下程序包 Npgsql.EntityFrameworkCore.PostgreSQ

踩坑–Springboot之Scheduled定时执行insert into …select联合语句

12-14

这类语句的作用是从一个或多个表中选择数据，并将这些数据插入到另一个表中。第一个查询用于聚合城市级别的数据，第二个查询则处理县（county）级别的数据。这两个查询都使用了JOIN操作连接了`nhm.nhm_interface_kpi...

【数据迁移】Sqlserver 迁移 Postgresql 详解 CSV+COPY 详细图文教程（实战踩坑优化案例）

A的博客

02-19

1402

迁移思路：先用sqlserver把新的项目去跑起来，然后去切postgresql去跑一下项目项目中一些持久层或者代码要做适配然后适配完去jmeter去压测，这个数据库性能能否支撑迁移，然后去设计postgre的表结构，然后去迁移存储过程函数等，然后迁移索引，然后调研迁移脚本，最后去做测试。在 SQL Server 中将数据导出为中间格式（如 CSV 或 SQL 脚本），推荐使用 CSV 文件，因为 PostgreSQL 的 COPY 命令可以高效导入。检查导出行数是否与数据库相同。

postgresql中根据唯一索引实现更新或新增

u014287925的博客

09-17

1343

postgresql提供的方法： ON CONFLICT DO UPDATE保证一个原子的 INSERT或者 UPDATE结果。在没有无关错误的前提下，这两种结果之一可以得到保证，即使在很高的并发度也能保证。这也可以被称作 UPSERT — “UPDATE 或 INSERT”。但是这个方法和oracle中merge into 的方法差异较大，只能单条数据更新或新增，且需要建主键，一般情况下入库的表不建议建主键，故用do$$代码块实现更新或新增的需要 do $$ declare

【PostgreSQL】PostgreSQL监控表的insert、update、delete、truncate操作，记录到监控日志表（可通用）

tttzzzqqq2018的博客

01-05

1445

【数据监控方案】【PostgreSQL】PostgreSQL监控表的insert、update、delete、truncate操作，记录到监控日志表（可通用）

Mysql存在唯一索引条件下，实现插入如果重复则自动更新

热门推荐

03-13

1万+

当我们需要将数据存入数据库的时候，由于我们不确定该数据是否已经存在数据库中，而无法确定是执行插入操作还是更新操作，通常我们会先根据建立了唯一索引的字段去查询，如果不存在，则选择插入，如果存在，则选择更新selet count(0) count from table where 唯一索引字段的条件 if count == 0 insert else update这里我们就需要先进行...

PostgreSQL插入数据时存在就更新，不存在就新增

m0_49986452的博客

08-18

3062

设置唯一约束索引 //约束名称随便写 ALTER TABLE 表名 ADD CONSTRAINT 约束名称 PRIMARY KEY (字段：如code) 插入数据时sql语句 //userName type code 是我拿到数据结构出来的可以写固定值来测试；code字段为上面设置的唯一约束。如果code值没有变就是修改，否则就是新增 INSERT INTO 表名(username, type, code) VALUES('${userName}', '${type}', '${code}') ON C

postgresql 数据库 INSERT 或 UPDATE 大量数据时速度慢的原因分析

gouzhengju1454的博客

07-18

5965

postgresql 数据库 INSERT 或 UPDATE 大量数据时速度慢的原因分析

postgresql 踩坑合集

q1042960848的博客

11-02

595

docker 启动 postgresql ，navicat'连接报错： FATAL: password authentucation failed for user ‘xxx’

Postgresql踩坑-磁盘空间不足

qq_31856061的博客

02-17

6794

引入在工作项目的服务器中，用的是postgresql数据库，一直以来并没有报过磁盘空间不足，但是今天早上回来发现ovirt管理端登录不上，排查发现pgsql服务没有了，没有人动过服务器，于是重新开启了pgsql服务，一切正常。但是为啥pgsql会自己关掉？去查看了pgsql的日志文件，发现它报了一个磁盘空间不足的错误。相关资料百度以下发现这问题还是有记录的背景：加压测试过程中发现插入数据过程中报错：could not write to hash-join temporary file: 设备上没

mysql多表批量插入数据根据唯一索引选择插入还是更新ON DUPLICATE KEY UPDATE

weixin_42736075的博客

08-09

834

在一次做项目后台统计的时候出现了一个问题：怎么从其他表（例如日志表）批量插入数据到统计表当中，我的数据库用的是mysql，而且这个时候还要考虑是是否存在该条记录选择插入还是更新，那个时候对我这个菜鸟还是有点难度的，后来查了一些资料，最好使用的是ON DUPLICATE KEY UPDATE，但是我查了半天没查到是将其他表批量插入的sql，倒是可以用mybatis通过<collection&g...

PgSQL upsert批量查询插入或更新(insert select/on conflict do update踩坑记录)

wujian_csdn_csdn的博客

12-06

4723

PGSQL数据库中根据唯一索引判断存在不存在，存在则更新，不存在就新增，可以参考下的sql，注意其中的item_type,item_type_id,item_group,item_group_key四个字段组成唯一的联合主键，没有这样的联合主键，就会报"there is no unique or exclusion constraint matching the ON CONFLICT specification"错误！ mapper: long saveAA(@Param("param") CreateC

pgsql 插入或更新

最新发布

04-10

<think>好的，用户想了解PostgreSQL中的插入或更新操作，也就是upsert的语法示例。首先我需要回忆一下PostgreSQL中相关的语法结构。记得在PostgreSQL中，upsert是通过INSERT语句的ON CONFLICT子句来实现的。这应该是在9.5版本之后引入的功能，用来处理唯一性约束冲突时的更新操作。用户提供的引用里提到，UPSERT是INSERT的特殊语法补充，当违反唯一性约束时，会转为UPDATE或no-op。比如引用[1]和引用[3]都提到了这一点。特别是引用[3]中的customers表，name字段有UNIQUE约束，这可以作为例子中的表结构。接下来需要给出具体的语法示例。基本结构应该是INSERT INTO ... VALUES ... ON CONFLICT (唯一列) DO UPDATE SET ...。例如，当插入数据时，如果name冲突了，就更新email字段。同时，可能需要使用EXCLUDED虚拟表来引用要插入的值。另外，用户可能需要知道如何处理多个唯一约束的情况。比如，如果表有多个唯一约束，需要在ON CONFLICT后面指定具体是哪个约束。比如使用ON CONFLICT ON CONSTRAINT约束名称，或者直接指定列名。还要注意，如果不需要更新，而是什么都不做的话，可以使用DO NOTHING。这在引用[1]中提到过，当违反唯一约束时，可以no-op。用户提供的示例表中，比如引用[2]中的upsert_test表，有id和name字段，可能需要用id作为主键或唯一约束，这时候的示例需要调整。但用户主要参考的是引用[3]的customers表，所以应该以那个为例。需要确保示例中的语法正确，比如使用正确的列名，加上WHERE条件可能让更新更灵活。比如只在某些条件下更新，或者排除某些情况。可能还需要提到冲突目标，比如必须是唯一性索引或约束，否则ON CONFLICT无法触发。比如主键、唯一约束或唯一索引。最后，生成相关问题的时候，应该围绕upsert的应用场景、与其它方法的区别、性能考虑等。比如用户可能想知道何时使用upsert，或者和INSERT ... ON DUPLICATE KEY UPDATE的区别，但PostgreSQL使用的是ON CONFLICT语法。</think>PostgreSQL 实现插入或更新操作（upsert）的核心语法是使用 `INSERT ... ON CONFLICT` 结构，以下是具体实现方法及示例： --- ### **语法说明** 1. **基础语法** ```sql INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...) ON CONFLICT (冲突目标) DO UPDATE SET 列1 = 表达式, 列2 = 表达式, ...; ``` - **冲突目标**：需指定唯一性约束的列或约束名称（如主键、唯一索引）。 - **表达式**：可使用 `EXCLUDED` 虚拟表引用插入时冲突的值[^3]。 2. **可选操作** - `DO NOTHING`：冲突时不执行任何操作。 - `DO UPDATE`：冲突时更新指定列。 --- ### **具体示例** #### **1. 基于唯一列的更新** 假设有表 `customers`（结构如下）： ```sql CREATE TABLE customers ( customer_id serial PRIMARY KEY, name VARCHAR UNIQUE, email VARCHAR NOT NULL, active bool DEFAULT TRUE ); ``` 插入数据时若 `name` 重复，则更新 `email`： ```sql INSERT INTO customers (name, email, active) VALUES ('Alice', 'alice@example.com', true) ON CONFLICT (name) DO UPDATE SET email = EXCLUDED.email, active = EXCLUDED.active; ``` #### **2. 使用 `DO NOTHING`** 冲突时直接忽略： ```sql INSERT INTO customers (name, email) VALUES ('Bob', 'bob@example.com') ON CONFLICT (name) DO NOTHING; ``` #### **3. 多列唯一约束** 若表存在联合唯一约束（如 `UNIQUE (id, name)`）： ```sql INSERT INTO upsert_test (id, name) VALUES (1, 'Charlie') ON CONFLICT (id, name) DO UPDATE SET name = EXCLUDED.name; ``` --- ### **注意事项** 1. **冲突目标必须明确**：需指定唯一约束的列名或约束名称（如 `ON CONFLICT ON CONSTRAINT 约束名`）。 2. **`EXCLUDED` 虚拟表**：引用插入时尝试写入的数据[^1]。 3. **性能优化**：建议在频繁更新的列上建立索引。 ---