分类：postgresql

2021
01-26

postgresql 计算距离的实例(单位直接生成米)

之前用的是ST_Distance函数，但是貌似需要进行一次单位的转换，而且网上有说那种转换不是特别准确，现在暂时将该算法记录在此：selectst_distance(ST_GeomFromText('POINT(120.45173736.520975)',900913),ST_GeomFromText('POINT(120.45563636.520885)',900913))*60*1.852;这里的计算方式倒是可以换坐标系，但是，测试了两个坐标系都没有起作用。而且该种方式转换过单位后跟arcgis计算出的结果相差甚远，最终决定使用下面的方... 继续阅读 >

2021
01-26

postgresql 除法保留小数位的实例

我就废话不多说了，大家还是直接看代码吧~\xselect8/(100-3)asc1,round(8/(100-3),4)asc2,round(8/(100-3)::numeric,4)asc3,8/(100-3)::numericasc4;-[RECORD1]--------------c1|0c2|0.0000c3|0.0825c4|0.08247422680412371134(1row)补充：PostgreSQL整数除法注意事项整型除以整型，正常情况下当然得到的应该也是整型。一般数据库中也是这么干的。在pg中也是这样，例如1/2得到的结果便会是0，但是在实... 继续阅读 >

2021
01-26

PostgreSQL 性能优化之服务器参数配置操作

大家好！我是只谈技术不剪发的Tony老师。今天我们来聊聊PostgreSQL的性能优化；数据库优化是一个系统的工程，本文只专注于服务器的参数配置优化。默认安装时，PostgreSQL的配置参数通常都偏小，不太适合作为生产服务器使用。所以，安装PostgreSQL数据库之后首先需要执行的操作就是对服务器的配置参数进行调整。查看/设置参数值我们使用PostgreSQL12，服务器的配置参数有300多个，运行时的参数值可以使用SHOW命令查看... 继续阅读 >

2021
01-26

Postgresql的select优化操作(快了200倍)

对于庞大的数据，检索sql的编写要格外小心，有很多平时不注意的sql可能就会变成瓶颈。比如,我们有个系统,其中t96_pd_log表，记录数8000w多，在开发阶段乃至用了那么多年都没问题,最近却发生频繁死锁的问题,查数据库后台发现问题出在一个select语句上,它耗时高达2.4-2.7s，这对于一个需要高并发的系统来说当然是致命的。数据表t96_pd_log有两条index，一条的字段组成是f96_mgtbarcd,另一条的字段组成是f96_result_type，检索... 继续阅读 >

2021
01-26

postgresql关于like%xxx%的优化操作

任何一个关系型数据库关于模糊匹配（like)的优化都是一件痛苦的事，相对而言，诸如like'abc%'之类的还好一点，可以通过创建索引来优化，但对于like'c%'之类的，真的就没有办法了。这里介绍一种postgresql关于like'c%'的优化方法，是基于全文检索的特性来实现的。测试数据准备（环境centos6.5+postgresql9.6.1）。postgres=#createtablets(idint,nametext);CREATETABLEpostgres=#\dtsTable"public.ts"Column|Type... 继续阅读 >

2021
01-26

PostgreSQL模糊匹配走索引的操作

场景lower(name)like'pf%'createtableusers(idintprimarykey,namevarchar(255));Createorreplacefunctionrandom_string(lengthinteger)returnstextas$$declarecharstext[]:='{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';resulttext:='';iinteger:=0;beginiflength<0thenraiseexception'Givenlengt... 继续阅读 >

2021
01-22

PostgreSQL 实现子查询返回多行的案例

作为一个表达式使用的子查询返回了多列：在查询中，我们需要以第2条查询语句作为第一条查询语句的条件，但是第一条根据这个条件查询出来是多个数据，这时候又需要保留多个数据，运用子查询就会报错，以下就为解决这种多对多关系查询，且没有关联关系的表的解决方案：selectc.rain_value,c.ad_nmfrom(select*,json::json->t2.lon_latasrain_valuefromactual_time_model_jsont1,(SELECTDISTINCTlon||'_'||lataslon... 继续阅读 >

2021
01-21

浅析postgresql 数据库 TimescaleDB 修改分区时间范围

今天遇到一个问题之前用TimescaleDB创建的超表是已7天为单位的由于7天的数据量过大影响查询效率需要把7天的分区改为一天一分区创建超表create_hypertable（）1.创建普通版表CREATETABLE"超表名"("collect_time"timestamp(6)NOTNULL,"code"varchar(36)COLLATE"pg_catalog"."default"NOTNULL,"value"numeric(14,4)NOTNULL,"create_time"timestamp(6)NOTNULL);2.改为超表create_hypertable（）SELECTcre... 继续阅读 >

2021
01-21

Postgresql分布式插件plproxy的使用详解

Simpleremotefunctioncall节点61/62(datanode)CREATETABLEusers(usernametext,emailtext);insertintousersvalues('user0','user0@gmail.com');insertintousersvalues('user1','user1@gmail.com');insertintousersvalues('user2','user2@gmail.com');节点60(proxy)createorreplaceextensionplproxy;CREATEFUNCTIONget_user_email(i_usernametext)RETURNSSETOFtextAS$$CONNECT'host=localhost... 继续阅读 >

2021
01-21

在PostgreSQL中设置表中某列值自增或循环方式

在postgresql中，设置已存在的某列(num)值自增，可以用以下方法：//将表tb按name排序，利用row_number()over()查询序号并将该列命名为rownum，创建新表tb1并将结果保存到该表中createtabletb1as(select*,row_number()over(orderbyname)asrownumfromtb);//根据两张表共同的字段name，将tb1中rownum对应值更新到tb中num中updatetbsetnum=(selecttb1.rownumfromtb1wheretb.name=tb1.name);//判断表tb1的存在... 继续阅读 >

2021
01-21

基于PostgreSQL密码重置操作

今天我在用命令行登陆postgresql的时候，忽然忘记密码了，重试了N次还是不行，然后我就试着上网找解决之道，最后完美的解决了，可是后来我才发现还有个更简单的方法，该篇博客用来记录自己沙雕行为。工具已登陆这个就是最简单的方法，你有工具登陆过，完全可以通过工具来修改（可是我当时完全没想到这个。。。），我是通过Navicat来连接的，可以直接通过Navicat来修改步骤如下：1、连接数据库2、点击角色3、选择角色4、填入新密码5... 继续阅读 >

2021
01-21

PostgreSQL 实现登录及修改密码操作

PostgreSQL登录1、可通过客户端pgAdminIII直接登录2、可通过命令行命令：psql-h10.10.10.10-Uuser-dpostgres-p5570-h：数据库IP-U：登录用户-d：登录的数据库-p：登录端口方法：进入postgreSQL的客户端安装目录（我的安装目录：C:\ProgramFiles\PostgreSQL\9.4\bin），执行psql命令，其中\q表示退出数据库修改密码直接执行以下sql即可修改密码alteruserpguserwithpassword'newpassword';补充：PostgreSQL修改密码... 继续阅读 >

2021
01-21

Postgresql 默认用户名与密码的操作

在UNIX平台中安装PostgreSQL之后，PostgreSQL会在UNIX系统中创建一个名为“postgres”当用户。PostgreSQL的默认用户名和数据库也是“postgres”，不过没有默认密码。在安装PostgreSQL之后可以以默认用户登录，也可以创建新当用户名。以默认用户登录Yu@Coat~$sudosupostgres#切换至postgrespostgres@Coat/home/Yu$psqlpostgres#登入默认数据库[sudo]passwordforYu:psql(9.1.6)Type"help"forhelp.以上命令... 继续阅读 >

2021
01-21

postgresql 中的加密扩展插件pgcrypto用法说明

近期测试了一下postgresql的加密扩展插件pgcrypto的aes加密安装加密扩展插件：pgcrypto在主节点上安装createextensionpgcrypto;aes加解密函数简单介绍encrypt(databytea,keybytea,typetext)--加密decrypt(databytea,keybytea,typetext)--解密data是需要加密的数据；type用于指定加密方法ASE方式加密：selectencrypt('postgres','abc','aes');解密：selectconvert_from(decrypt('\xd664687424b2806001d0744177284... 继续阅读 >

2021
01-21

PostgreSQL表膨胀监控案例(精确计算)

膨胀率的精确计算PostgreSQL自带了pgstattuple模块，可用于精确计算表的膨胀率。譬如这里的tuple_percent字段就是元组实际字节占关系总大小的百分比，用1减去该值即为膨胀率。#插入1000W数据postgres=#insertintotselectid,idfromgenerate_series(1,10000000)asid;INSERT010000000#表膨胀系数为0.097postgres=#select*,1.0-tuple_len::numeric/table_lenasbloatfrompgstattuple('t');table_len|tuple_co... 继续阅读 >

2021
01-21

postgresql查看表和索引的情况,判断是否膨胀的操作

索引膨胀的几个来源：1大量删除发生后，导致索引页面稀疏，降低了索引使用效率。2PostgresQL9.0之前的版本，vacuumfull会同样导致索引页面稀疏。3长时间运行的事务，禁止vacuum对表的清理工作，因而导致页面稀疏状态一直保持。查看重复索引SELECTpg_size_pretty(SUM(pg_relation_size(idx))::BIGINT)ASSIZE,(array_agg(idx))[1]ASidx1,(array_agg(idx))[2]ASidx2,(array_agg(idx))[3]ASidx3,(array_agg(idx))... 继续阅读 >