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可以是下面列举内容之一：

  • (column_name) – 列名
  • ON CONSTRAINT constraint_name – 唯一约束的名称
  • WHERE predicate – 带谓词的where子句.

action可能为下面两者之一：

DO NOTHING – 如果行已存在表中，不执行任何动作.
DO UPDATE SET column_1 = value_1, … WHERE condition – 更新表中一些字段.

注意：ON CONFLICT子句仅从PostgreSQL 9.5版本才有效。如果需用在之前版本，需要使用其他方法实现。

2. PostgreSQL upsert示例

下面语句创建customers表，演示PostgreSQL upsert特性：

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
	customer_id serial PRIMARY KEY,
	name VARCHAR UNIQUE,
	email VARCHAR NOT NULL,
	active bool NOT NULL DEFAULT TRUE
);

customers 表包括四个字段customer_id, name, email, active，name字段有唯一约束确保名称唯一。

下面插入语句新增几条记录：

INSERT INTO
 customers (name, email)
VALUES
 ('IBM', 'contact@ibm.com'),
 ('Microsoft', 'contact@microsoft.com'),
 ('Intel', 'contact@intel.com');

假设现在Microsoft 修改email字段，从 contact@microsoft.com 到 hotline@microft.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;

上面语句表示如果名称表中存在，则什么都不做。下面语句与上面等价，但使用name列代替唯一约束名称：

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING;

假设当记录已存在时你需要连接新的邮箱至原邮箱，这时update动作：

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com')
ON CONFLICT (name)
DO
 UPDATE SET email = EXCLUDED.email || ';' || customers.email;

这里使用EXCLUDED虚拟表，其包含我们要更新的记录，也就是新记录（相对于原记录customers）。等式右边字段需要表名进行区分，才不会报字段不明确错误。
读者可以自行验证结果是否如你所愿。

3. 总结

本文介绍通过INSERT ON CONFLICT实现PostgreSQL插入更新特性。

到此这篇关于PostgreSQL upsert(插入更新)教程详解的文章就介绍到这了,更多相关PostgreSQL upsert内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

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

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

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

  • PostgreSQL的upsert实例操作(insert on conflict do)

    PostgreSQL的upsert实例操作(insert on conflict do)

    建表语句: DROP TABLE IF EXISTS "goods"; CREATE TABLE "goods" ( "store_cd" int4 NOT NULL, "good_cd" varchar(50) COLLATE "pg_catalog"."default" NOT NULL, "name" varchar(255) COLLATE "pg_

  • Python随机生成数据后插入到PostgreSQL

    用Python随机生成学生姓名,三科成绩和班级数据,再插入到PostgreSQL中. 模块用psycopg2 random import random import psycopg2 fname=['金','赵','李','陈','许','龙','王','高','张','侯','艾','钱','孙','周','郑'] mname=['玉','明','玲','淑','偑','艳','大','小','风','雨','雪','天','水','奇','鲸','米','晓','泽','恩','葛','玄'

  • 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可以是下

  • MySQL中数据视图操作详解

    目录 1.视图概述 1.1创建视图 1.2视图的查询 2.操作视图 2.1通过视图操作数据 2.2修改视图定义 2.3删除视图 1.视图概述 视图是从一个或多个表(或视图)导出的表.视图与表(有时为与视图区别,也称表为基本表)不同,视图是一个虚表,即视图所对应的数据不进行实际存储,数据库中只存储视图的定义,对视图的数据进行操作时,系统根据视图的定义去操作与视图相关联的基本表. 视图一经定义,就可以像表一样被查询.修改.删除和更新.使用视图有下列优点: 1.为用户集中数据,简化用户的数据查询和处理

  • SpringBoot yaml语法与数据读取操作详解

    SpringBoot yaml语法与数据读取操作详解

    目录 yaml yaml语法规则 字面值表示方式: 数组表示方式: 对象数组格式: 对象数组缩略格式: 读取yaml数据 编写yaml文件 读取单一数据 读取二级数据 读取数组数据 读取服务器端口号 读取对象属性 封装全部数据到Environment对象 读取yaml引用类型属性数据 application.yml MyDataSource 读取数据 变量的引用 application.yml 读取数据 context-path @Autowired报错解决方案 yaml YAML是一种数据序列

  • CentOS7系统搭建LAMP及更新PHP版本操作详解

    本文实例讲述了CentOS7系统搭建LAMP及更新PHP版本操作.分享给大家供大家参考,具体如下: 搭建LAMP环境 用yum安装 安装Apache 安装Apache [root@localhost /]# yum install httpd httpd-devel 启动Apache [root@localhost /]# systemctl start httpd 设置Apache开机启动 [root@localhost /]# systemctl enable httpd Created s

  • MongoDB数据库插入、更新和删除操作详解

    MongoDB数据库插入、更新和删除操作详解

    一.Insert操作 Insert操作是MongoDB插入数据的基本方法,对目标集合使用Insert操作,会将该文档添加到MongoDB并自动生成相应的ID键.文档结构采用类似JSON的BSON格式.常见的插入操作主要有单条插入和批量插入两种形式.插入时只是简单地将文档存入数据库中,不进行额外的验证,也不会执行代码,所以不存在注入式攻击的可能. 1.单条插入 2.批量插入 MongoDB对批量插入的支持是通过传递多个文档组成的数组到数据库来实现的.由于它插入数据是通过发送TCP请求的,这样只需发

  • MongoDB中的push操作详解(将文档插入到数组)

    目录 1. 概述 2. 数据库初始化 3. 使用 Mongo Query 进行推送操作 4. 使用Java驱动代码进行推送操作 4.1. 使用 DBObject 4.2. 使用 BSON 文档 5. 使用 addToSet操作符 5.1. 使用addToSet运算符的 Shell 查询 5.2. 使用addToSet运算符的 Java 驱动程序 6. 结论 总结 1. 概述 在本教程中,我们将介绍如何在MongoDB中将文档插入到数组中.此外,我们将看到 $push 和 $addToset 运算

  • 实操MySQL+PostgreSQL批量插入更新insertOrUpdate

    目录 一.百度百科 1.MySQL 2.PostgreSQL 3.PostgreSQL相对于MySQL的优势 二.postgres中insertOrUpdate代码实例 1.创建user表 2.简单的方式实现 3.利用unnest函数实现 4.如果数据已存在,就就什么也不做 三.相关重点函数简介 1.unnest(anyarray) 四.userMapper.xml写法 五.MySQL中insertOrUpdate代码实例 1.建表语句 2.普通方式 3.ON DUPLICATE KEY UPD

  • Python必备技巧之字符数据操作详解

    Python必备技巧之字符数据操作详解

    目录 字符串操作 字符串 + 运算符 字符串 * 运算符 字符串 in 运算符 内置字符串函数 字符串索引 字符串切片 字符串切片中的步幅 将变量插入字符串 修改字符串 内置字符串方法 bytes对象 定义文字bytes对象 bytes使用内置bytes()函数定义对象 bytes对象操作,操作参考字符串. bytearray对象,Python 支持的另一种二进制序列类型 字符串操作 字符串 + 运算符 +运算符用于连接字符串,返回一个由连接在一起的操作数组成的字符串. >>> s =

  • vue.js前后端数据交互之提交数据操作详解

    本文实例讲述了vue.js前后端数据交互之提交数据操作.分享给大家供大家参考,具体如下: 前端小白刚开始做页面的时候,我们的前端页面中经常会用到表单,所以学会提交表单也是一个基本技能,其实用ajax就能实现,但他的原始语法有点...额 ...复杂,所以这里给大家提供一种用vue-resource向后端提交数据. (1)第一步,先在template中写一个表单: <el-form :model="ruleForm" :rules="rules" ref=&quo

  • 对python 操作solr索引数据的实例详解

    测试代码1: def test(self): data = {"add": {"doc": {"id": "100001", "*字段名*": u"我是一个大好人"}}} params = {"boost": 1.0, "overwrite": "true", "commitWithin": 1000} ur

随机推荐