PostgreSQL
文章平均质量分 85
PostgreSQL各种知识梳理
面汤放盐
这个作者很懒,什么都没留下…
展开
-
不同数据库表之间的数据同步
不同数据库表之间的数据同步场景还原: 现在需要对正式环境做升级,需要从实施环境往正式环境导入部分数据,现在需要比对两个数据库之间某个表的数据差异,并将正式环境缺少的部分,从实施环境同步到正式环境。现在就需要这部分数据。但是,如果有差异的数据是有顺序的,比如按照时间顺序、按照主键自增等,找到有差异的部分会很容易。但是两个都不满足呢?在生产环境上把表备份一下,直接truncate ,将实施环境的...原创 2019-06-05 16:35:06 · 22920 阅读 · 2 评论 -
PostgreSQL函数(存储过程)返回多条记录的实现方式
29/100保存草稿发布文章 博文管理我的博客退出 Trash Temp 集线器,交换机,路由器之间有什么区别 集线器,交换机,路由器之间有什么区别 集线器,交换机,路由器之间有什么区别 集线器,交换机,路由器之间有什么区别 计算机网路基础 计算机网路基础 计算机网...原创 2020-02-07 16:31:42 · 7248 阅读 · 0 评论 -
PostgreSQL完成按月累加
PostgreSQL完成按月累加背景统计某个指标,指标按照月进行累加,注意需要按省份和年份进行分组。方法一、使用自关联-- with 按月统计得到中间结果WITH yms AS (SELECT regionid,SUM(getnum) AS getnum,SUM(dealnum) AS dealnum,to_char(qndate,'yyyy-MM') AS yearmonthFRO...原创 2020-01-07 14:27:40 · 4756 阅读 · 3 评论 -
PostgreSQL给查询列表增加序号
PostgreSQL给查询列表增加序号利用 ROW_NUMBER() over( ) 给查询序列增加排序字段SELECT ROW_NUMBER() over(ORDER bY biztypename DESC ) AS num,biztypename FROM (SELECT DISTINCT biztypename FROM bizmaptype) t;效果如下:...原创 2020-01-02 16:25:18 · 12163 阅读 · 0 评论 -
PostgreSQL修改字段类型(表中存在数据)
PostgreSQL修改字段类型(表中存在数据)将表t_appraisescore 字段appraisedate 从varchar 修改成datealter table t_appraisescore alter column appraisedate type date using appraisedate::date;...原创 2019-12-28 21:13:47 · 7455 阅读 · 2 评论 -
PostgreSQL之raise(打印文本)
PostgreSQL之raise一、raise打印文本raise notice 'bgtimestamp is % ,bgdate is %, bgyearmonth is %',bgtimestamp,bgdate,bgyearmonth;原创 2019-12-23 21:14:36 · 1483 阅读 · 0 评论 -
shell+crontab制作postgresql数据库定期备份脚本
使用shell脚本+crontab制作定期脚本备份业务背景在开发期间,定期对数据库备份是非常有必要的,下面制作了粗来的脚步。第一步:编写shell脚本#! /bin/sh#获取一个时间戳 ymdhm=`date +%Y%m%d%H%M`echo $timestamp $ymdhm#创建文件夹mkdir -p /home/pgdatabackups/$ymdhm#路径ba...原创 2019-12-22 18:33:21 · 609 阅读 · 0 评论 -
使用bat 编写postgresql自动备份脚步
使用bat 编写postgresql自动备份脚步背景需求每天需要手动备份一个数据库到本地,每次都需要切换的postgresql/bin 下面,创建文件夹,然后输入一串的备份命令,再输入密码;每天都要这么做感觉麻烦,所以编写了一个bat脚步bat脚步如下:postgresql_backup.bat,set current_path=%~dp0 ::获取日期时分set directo...原创 2019-12-22 18:09:32 · 1335 阅读 · 0 评论 -
PostgreSQL 使用其他表作为模板创建新表
使用其他表作为模板创建新表创建表tbl_big 并利用该表为模板,快速创建表 ( like tbl_big including all )[root@uzong ~]# su postgresbash-4.2$ psql technologycould not change directory to "/root"psql (9.2.24, server 10.8)WARNING: p...原创 2019-08-05 20:47:01 · 1134 阅读 · 0 评论 -
PostgreSQL更新JSON中的某个字段
PostgreSQL更新JSON中的某个字段背景需求现在需要通过SQL的方式,批量对JSON里面的某个字段统一处理,更新成一个新值。方法一 、使用jsonb_set函数补充:读取JSON中的值。select ‘{“a”:“foo”,“b”:1}’::json->‘a’; – 获得键’a’的值select ‘{“a”:“foo”,“b”:1}’::json->>‘a’...原创 2019-07-17 09:24:45 · 13712 阅读 · 1 评论 -
PostgreSQL简单的数据库备份
pg的一些简单的运维知识centos7 、PostgreSQL10一、粗略的数据库备份脚本粗略的备份某个数据,例如:在每天23点备份这个 technology 数据库。第一步:编写shell脚本 : backup.sh#! /bin/shda=`date +%Y%m%d%H%M%S`echo $dapg_dump mydb > /var/lib/pgsql/10/...原创 2019-07-03 20:05:47 · 492 阅读 · 0 评论 -
Postgresql中的string_to_array 和 array_length 应用
Postgresql中的string_to_array 和 array_length 应用业务诉求,计算path路径的层次,如下图所示:select array_length(string_to_array(org.codepath,'.'), 1)-1 as level ,string_to_array(org.codepath,'.'),org.codepathfrom orgs...原创 2019-06-18 14:25:20 · 17840 阅读 · 0 评论 -
PostgreSQL中的 nulls first 和 nulls last
一、 nulls first 和 nulls lastnulls first 排序指定让null 放在最前nulls last 排序指定null放在最后二、使用-- 1. 默认null放在最后select positionid, * from struct ORDER BY positionid;-- 2. null 最后select positionid, * from st...原创 2019-06-10 17:12:21 · 4718 阅读 · 0 评论 -
postgresql查询某个字段属于哪个表
一、场景还原前端小林问我,它从前端的返回的某些字段,想知道那个表有这个字段,它想操作这个表……二、sql语句SELECT *FROM information_schema. COLUMNSWHERE COLUMN_NAME = 'storename';SELECT c.relname AS "表名",a.attname AS "字段名",c.oidFROM pg_clas...原创 2019-06-10 15:37:45 · 5577 阅读 · 1 评论 -
看PostGIS 如何解决地理围栏问题
PostGIS空间数据时一类重要的数据,地图导航、打车软件、餐厅推荐、外卖快递。 空间数据通常结构复杂,数据量大,对于空间数据的分析查询,其模式也跟一般DBMS不同一、安装与配置环境信息:PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-...原创 2019-04-20 18:07:13 · 2400 阅读 · 0 评论 -
描述一个postgresql中聚集函数array_agg、string_agg、bit_and、bit_or的应用场景
postgresql中的聚集函数array_agg、string_agg、bit_and、bit_or等聚集函数从一个输入值的集合计算一个单一结果。1.1 业务场景业务场景如下图所示:销售区域可以有多个渠道类型、多个负责部门、多个负责人销售区域 + 多个渠道类型 形成唯一值。(新增、编辑时需要做数据校验)第一个问题就非常适合使用 string_agg() 函数。针对第二个问题...原创 2019-05-27 23:07:10 · 1619 阅读 · 0 评论 -
PostgreSQL中的crosstab(行转列函数)
PostgreSQL中的crosstab(行转列函数)行转列是一个非常有用的功能,如果不适用行转列函数,则通用做法是使用 case when 。不过,接下来,介绍一下今天的新主角–crosstab一、安装扩展环境:centos7 、pg10(yum 安装)、扩展组件 tablefunccrosstab 需要按照扩展tablefunc[root@uzong ~]# su postgre...原创 2019-05-27 12:35:43 · 27427 阅读 · 10 评论 -
PostgreSQL中的postgres_fdw扩展
PostgreSQL中的postgres_fdw扩展通过postgres_fdw 扩展,访问远程数据库表一、环境准备虚拟机(node107):centos7、PostgreSQL10远程服务器(百度云服务BBC): centos7、PostgreSQL10在本地虚拟机上访问远程服务器的数据表。二、配置连接(1)创建扩展: 在本地107这个节点上创建扩展。[root@107 ...原创 2019-05-26 23:40:52 · 9291 阅读 · 1 评论 -
PostgreSQL如何实现MVCC (基于xmin、xmax、cmin、cmax)
声明:本文是《PostgreSQL实战》读书笔记,参考了http://www.jasongj.com/sql/mvcc/ 部分,可以参考该书事务与并发控制章节 和 http://www.jasongj.com/sql/mvcc/PostgreSQL如何实现MVCC (基于xmin、xmax、cmin、xmax)一、基于多版本的并发控制在PostgreSQL中,会为每一个事务分配一个递增的、类...原创 2019-05-26 01:29:38 · 3723 阅读 · 0 评论 -
解决PostgreSQL表膨胀
转载地址:https://www.timbotetsu.com/blog/postgresql-bloatbusters/PostgreSQL表膨胀终结者无论是DBA还是开发者,只要工作中使用PostgreSQL,多多少少都会遇到vacuum:DBA需要配置vacuum,开发者遇到因vacuum导致的数据库性能下降、慢查询问题。背景vacuum对开发者来说是个黑盒,DBA知道vac...转载 2019-05-25 21:08:47 · 8067 阅读 · 0 评论 -
PostgreSQL10启动和停止数据库服务
# 状态查看service postgresql-10 status # 停止service postgresql-10 stop# 启动service postgresql-10 start停止命令,查看状态启动命令,查看状态原创 2019-05-25 17:40:18 · 10256 阅读 · 0 评论 -
PostgreSQL中表大小、索引大小
一、PostgreSQL中表大小、索引大小需要了解每张表的具体大小,PostgreSQL提供了很多工具1.1 来查看任意表的磁盘用量每个表都有一个主要的堆磁盘文件,大多数数据都存储在其中。如果一个表有着可能会很宽(尺寸大)的列, 则另外还有一个TOAST文件与这个表相关联, 它用于存储因为太宽而不能存储在主表里面的值(http://www.postgres.cn/docs/9.6/stor...原创 2019-05-25 17:29:01 · 3284 阅读 · 0 评论 -
PosgreSQL如果不存在则插入,存在则更新(UPSET 特性)
一、背景某客户上线一段时间后,日志中出现大量主键冲突的日志,并且出现数据库繁忙。ERROR: duplicate key value violates unique constraint “visit_workrecord_pkey”Detail: Key (recordid)=(1105661718883) already exists.问题: 此类日志会大量出现。二、UP...原创 2019-04-14 21:22:50 · 5216 阅读 · 0 评论 -
pg查看某个表的所有索引信息
select * from pg_indexes where tablename='student';select * from pg_statio_all_indexes where relname='student';原创 2019-04-02 19:42:43 · 21040 阅读 · 0 评论 -
查Postgresql 数据库占用磁盘大小
查看数据库占用磁盘大小 SELECT d.datname AS "实例名", pg_catalog.pg_get_userbyid(d.datdba) AS "所属者", CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_ca...原创 2019-03-26 15:31:00 · 5312 阅读 · 0 评论 -
新建数据库失败,source database template1 is being accessed by others users
template1模板正在被人连接select pg_terminate_backend(pid) from pg_stat_activity where DATNAME = 'template1';原创 2019-03-04 15:59:35 · 1683 阅读 · 0 评论 -
postgresql 日期函数使用场景
postgresql 日期函数一、本月第一天在报表抽取时,常常需要获取本月第一天。然后作为报表抽取的查询条件。第一天常常有两种格式,一种是日期,另外一种是时间戳。-- 本月。如2019-02SELECT to_char(now(),'YYYY-MM')-- 本月第一天,日期格式 。 例如 2019-02-01SELECT to_char(now(),'YYYY-MM-01')--...原创 2019-02-26 11:28:10 · 1224 阅读 · 0 评论 -
postgresql获取表结构,表名、表注释、字段名、字段类型及长度和字段注释(转载)
转载地址:https://blog.csdn.net/weixin_38924323/article/details/80982760 场景描述:navicate 将postgresql表结构导出到Excel。1、查询表名和表注释select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varc...转载 2018-12-26 13:00:50 · 19513 阅读 · 9 评论 -
PostgreSQL 存储过程异常处理(转载)
转载地址:https://www.cnblogs.com/lottu/p/7410978.html异常错误处理在PL/pgSQL函数中,如果没有异常捕获,函数会在发生错误时直接退出,与其相关的事物也会随之回滚。我们可以通过使用带有EXCEPTION子句的BEGIN块来捕获异常并使其从中恢复。见如下声明形式:[ <> ][ DECLARE declarations ]B...转载 2018-12-09 20:46:04 · 8111 阅读 · 0 评论 -
postgresql 技术内幕学习笔记
第一章 概述1.1 查询优化的简介一个查询优化器它的输入是查询树,输出是查询执行计划通常数据库的查询优化分为两个层次:基于规则的查询优化(逻辑优化:rule based optimization)基于代价的查询优化 (物理优化,cost based optimization)...转载 2018-12-08 23:27:00 · 1493 阅读 · 0 评论 -
PostgreSQL 序列(SEQUENCE)
参考这篇文章:PostgreSQL 序列(SEQUENCE)转载 2018-12-08 21:41:45 · 1103 阅读 · 0 评论 -
PostgreSQL的中文拼音排序(转载)
转载地址: https://my.oschina.net/Kenyon/blog/183063环境: OS:CentOS 6.3DB:PostgreSQL 9.2.4TABLE: tbl_kenyon场景:postgres=# \d tbl_kenyon Table "public.tbl_kenyon" Column | Type | Modifiers --...转载 2018-12-01 16:31:19 · 5963 阅读 · 0 评论 -
数据库排序分页串行问题
业务场景数据库排序分页;因为排序时,指定字段不能确定数据表的顺序。第一页返回的数据和第二页返回的数据相等。导致数据串行。参看文字PostgreSQL 排序后 Limit OFFSET 分页 串行 问题解决办法:...原创 2018-12-01 16:29:35 · 394 阅读 · 0 评论 -
postgresl系列之服务器命令和操作系统优化
本文是《PostgreSQL实战》的读书笔记,感兴趣的可以参看本书对应章节。常用的linux性能工具Linux操作系统提供了非常丰富的性能监控工具,可以全方位监控CPU、内存、虚拟机内存、磁盘I/O、网络等各项指标。本文主要讲解常用的性能检测工具,例如,top、free、vmstat、iostat、mpstat、sar、pidstat等。除了top和free外,其他工具均位于systat包中。...原创 2018-12-01 14:48:05 · 1723 阅读 · 0 评论 -
postgresl系列之NoSQL特性
本文是《PostgreSQL实战》的读书笔记,感兴趣的可以参看本书对应章节。一、PostgreSQL的NoSQL特性PostgreSQL不只是一个关系型数据库,同时支持非关系特性,而且逐步增加对非关系特性的支持。1.1 为jsonb类型创建索引jsonb数据类型支持GIN索引。{ "id":1, "user_id":122, "user_name":"原创 2018-11-30 23:49:05 · 1766 阅读 · 0 评论 -
postgresl系列之分区表
本文是《postgresql实战》的读书笔记,感兴趣可以参考本书对应章节一、分区表在10版本前通过继承加上触发器实现分区表,步骤繁琐,10版本增加了内置分区,支持范围分区和列表分区。1.1 分区表的意义降低大表管理成本和某些场景的性能提升。...原创 2018-11-25 20:29:08 · 997 阅读 · 0 评论 -
postgresl系列之事务与并发控制
本文是《postgresql实战》的读书笔记,感兴趣可以参考本书对应章节一、事务与并发控制事务在关系型数据库中十分重要;而并发则带来了更大的吞吐量、资源利用率 和 更好的性能。但是当多个事务并发执行时,即使每个单独的事务都正确执行,数据库一致性也可能被破坏。为了控制并发事务之间的相互影响,解决并发可能带来的资源争用及数据不一致问题,数据库引入了基于锁的并发控制机制(Lock-Based Con...原创 2018-11-22 23:10:22 · 696 阅读 · 0 评论 -
postgresql系列之并行查询
本文是《postgresql实战》的读书笔记,感兴趣可以参考该书对应章节一、并行查询postgresql在9.6开始支持并行查询,但支持的范围非常有限,在postgresql10得到进一步了增强。1.1 并行查询相关参数参数描述max_work_processer(integer)设置系统支持的最大后台进程,默认值为8,此参数调整后需要重启数据库才生效max_p...原创 2018-11-20 13:47:53 · 6362 阅读 · 0 评论 -
卸载和安装postgresql
因为postgresql10增强了并行查询,而postgresql9.6 并行只支持了一部分,现在需要卸载pg9.6然后安装pg10注意:centos 7 通过yum方式卸载和安装一、卸载postgresql9.6通过以下命令查看已经安装的Postgresql软件包[root@107 ~]# rpm -qa | grep postgresqlpostgresql96-9.6....原创 2018-11-20 12:57:18 · 8154 阅读 · 0 评论 -
《sql基础教程》阅读笔记
本文是《sql基础教程》阅读笔记;感兴趣可以阅读该书对应章节一、select 语句基础1.1 别名的使用别名可以使用中文,使用中文时需要用双引号(&quot;)括起来A。请注意不是单引号(’)素材准备CREATE TABLE Product(product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL,...原创 2018-11-19 19:37:24 · 389 阅读 · 0 评论