分类：PostgreSQL

2021
01-29

postgresql 实现replace into功能的代码

PostgreSQL9.5-使用函数或with实现createtabletest(idintprimarykey,infotext,crt_timetimestamp);withupsertas(updatetestsetinfo='test',crt_time=now()whereid=1returning*)insertintotestselect1,'test',now()wherenotexists(select1fromupsertwhereid=1);PostgreSQL9.5+PostgreSQL9.5引入了一项新功能，UPSERT(insertonconflictdo)，当插入遇到约束错误时，直接返回，或者改为执行U... 继续阅读 >

2021
01-29

postgresql 替换空格 换行和回车的操作

我就废话不多说了，大家还是直接看代码吧~UPDATEtablesetname=trim(name);//用来删除数据前后的空格UPDATEtablesetname=rtrim(name);//用来删除数据前的空格UPDATEtablesetname=ltrim(name);//用来删除数据后的空格UPDATEtablesetname=REPLACE(name，'','');//用来删除数据中的空格UPDATEtablesetname=REPLACE(name,CHR(10),'')//替换换行符UPDATEtablesetname=REPLACE(name,CHR(13),'')/... 继续阅读 >

2021
01-27

PostgreSQL 远程连接配置操作

postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。打开安装目录下的data文件，我一般安装在D盘，我安装的postgre是64位，所以，安装目录为D:\ProgramFiles\PostgreSQL\9.5\data。我们要更改的文件有两个，分别为pg_hba.conf和postgresql.conf，一般需要进行三步，但也可能是四步。1.用记事本打开pg_hba.conf，在最后加上以下两句话#TYPEDATABASEUSERCIDR-ADDRESSMETHODhostall... 继续阅读 >

2021
01-27

postgreSQL中的内连接和外连接实现操作

测试数据：city表：createtablecity(idint,nametext);insertintocityvalues(0,'北京'),(1,'西安'),(2,'天津'),(3,'上海'),(4,'哈尔滨'),(5,'西藏')person表：createtableperson(idint,lastnamechar(20));insertintopersonvalues(0,'Tom'),(2,'Lily'),(3,'Mary'),(5,'Coco');select*fromcity;select*fromperson;一：内连接：1.innerjoininnerjoin(等值连接)只返回两个表中联结字段相等的行sql语句：select*... 继续阅读 >

2021
01-27

postgreSQL中的case用法说明

工具：postgreSQLNavicatPremium又一次在看代码的时候，发现了不懂的东西！这次就是casewhenthenSQLCASE表达式是一种通用的条件表达式，类似于其它语言中的if/else语句。CASEWHENconditionTHENresult[WHEN...][ELSEresult]END解释：condition是一个返回boolean的表达式。如果为真，那么CASE表达式的结果就是符合条件的result。如果结果为假，那么以相同方式搜寻随后的WHEN子句。如果没有WHENcondition为真，那么... 继续阅读 >

2021
01-27

基于postgreSql 常用查询小结

1.日期格式转化（参考）selectbeg_time,end_time,extract(epochfromto_timestamp(end_time,'yyyy-mm-dd-HH24-MI-SS-US'))-extract(epochfromto_timestamp(beg_time,'yyyy-mm-dd-HH24-MI-SS-US'))fromcdb_all_iu_datawherebeg_time>'2017-09-21'注：beg_time,end_time以TEXT形式存储，求时间差时转化为时间戳再相减得到结果（s）2.select*from(中间结果)tselectcount(*)from(selectchkid,count(*)froma... 继续阅读 >

2021
01-27

postgresql 查询字符串中是否包含某字符的操作

先看表结构和数据：需求：查询出manager_id中包含某些字符的数据，此处查询manager_id包含6651545956882725395，5722501350582149881的数据，SQL语句如下：SELECTt.*frombas_cm_customertwhereposition('6651545956882725395'int.manager_id)>0ORposition('5722501350582149881'int.manager_id)>0;SELECTt.*frombas_cm_customertwhereposition('6651545956882725395'int.manager_id)>0ORposition('5... 继续阅读 >

2021
01-27

PostgreSQL 自动Vacuum配置方式

PostgreSQL的Vacuum由于以下原因需要定期执行。释放,再利用因更新或者删除更新而占用的磁盘空间。更新PostgreSQL查询计划用的统计数据。避免事务ID的重置而引起非常老的数据丢失。VACUUM的标准SQL文的执行和其他的对数据库的实际操作可以并行处理。SELECT、INSERT、UPDATE、DELETE等命令和同通常一样继续能够执行。但是，VACUUM处理中的时候，ALTERTABLEADDCOLUMN等等的命令不能够对表进行重新定义。还有，由于执行VACUU... 继续阅读 >

2021
01-27

PostgreSQL 慢查询SQL跟踪操作

PostgreSQL开启慢SQL捕获在排查问题时是个很有效的手段。根据慢SQL让我在工作中真正解决了实际问题，很有帮助。PostgreSQL日志支持的输出格式有stderr（默认）、csvlog、syslog一般的错误跟踪，只需在配置文件【postgresql.conf】简单设置几个参数，当然还有错误级别等要设置。logging_collector=onlog_destination='stderr'log_directory='log'log_filename='postgresql-%Y-%m-%d_%H%M%S.log'SELECTname,sett... 继续阅读 >

2021
01-27

CentOS PostgreSQL 12 主从复制(主从切换)操作

主从复制1.基于文件的日志传送创建一个高可用性（HA）集群配置可采用连续归档，集群中主服务器工作在连续归档模式下，备服务器工作在连续恢复模式下（1台或多台可随时接管主服务器），备持续从主服务器读取WAL文件。连续归档不需要对数据库表做任何改动，可有效降低管理开销，对主服务器的性能影响也相对较低。直接从一个数据库服务器移动WAL记录到另一台服务器被称为日志传送，PostgreSQL通过一次一文件（WAL段）的WAL记录传输实... 继续阅读 >

2021
01-27

PostgreSQL 查看表的主外键等约束关系详解

我就废话不多说了，大家还是直接看代码吧~SELECTtc.constraint_name,tc.table_name,kcu.column_name,ccu.table_nameASforeign_table_name,ccu.column_nameASforeign_column_name,tc.is_deferrable,tc.initially_deferredFROMinformation_schema.table_constraintsAStcJOINinformation_schema.key_column_usageASkcuONtc.constraint_name=kcu.constraint_nameJOINinformation_schema.cons... 继续阅读 >

2021
01-27

PostgreSQL 修改视图的操作

最近发现PostgreSQL（createorreplace）修改视图有很多的限制，不像SQLServer和Oracle那样可以随意修改。错误提示修改视图原有字段ERROR:cannotchangenameofviewcolumn"user_id"to"?column?"删除视图原有字段ERROR:cannotdropcolumnsfromview找到如下原因，内容原文究其原因，是PostgreSQL虽然支持CREATEORREPLACEVIEW语义，却有着容易让人忽略的重要限制(Oracle没有该限制)，其官方文档这样描述：即... 继续阅读 >

2021
01-27

PostgreSQL 更新视图脚本的注意事项说明

项目最早是基于Oracle的，移植到PostgreSQL后，本着尽量少修改的原则，创建/更新视图的脚本也沿用了Oracle风格的CREATEORREPLACEVIEW形式。但是每当要更新视图定义时，常常报"cannotchangenameofviewcolumnxxxtoyyy"的错误，通常是在视图修改某字段名、中间增加字段、删除字段时发生。究其原因，是PostgreSQL虽然支持CREATEORREPLACEVIEW语义，却有着容易让人忽略的重要限制(Oracle没有该限制)，其官方文档这样描述... 继续阅读 >

2021
01-27

postgreSQL中的row_number() 与distinct用法说明

我就废话不多说了，大家还是直接看代码吧~selectcount(s.*)from(select*,row_number()over(partitionbyfee_dateorderbyfee_date)asgrfromnew_orderwherenews_id='novel'andorder_status='2')swheres.gr=1SELECTcount(DISTINCTfee_date)asdisfromnew_orderwherenews_id='novel'andorder_status='2'这两个SQL执行所得到的数据是一样的！工具：postgreSQL1.我们要清楚，sql的执行顺序：from语... 继续阅读 >

2021
01-27

Postgresql 动态统计某一列的某一值出现的次数实例

实例解析：selectto_char(log.date,'yyyy-MM-ddHH24')ashour,log.exten,sum(caselog.gradewhen'1'then1else0end)as"1",sum(caselog.gradewhen'2'then1else0end)as"2",sum(caselog.gradewhen'3'then1else0end)as"3",sum(caselog.gradewhen'4'then1else0end)as"4",sum(caselog.gradewhen'5'then1else0end)as"5",log.directionfromiface_satisfaction_investi... 继续阅读 >

2021
01-26

postgresql 计算两点距离的2种方法小结

postgresql计算两点距离下面两种方法：selectST_Distance(ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography,ST_SetSRID(ST_MakePoint(106.00231199774656,29.719258550486572),4326)::geography),ST_Length(ST_MakeLine(ST_MakePoint(115.97166453999147,28.716493914230423),ST_MakePoint(106.00231199774656,29.719258550486572))::geography)备注：ST_GeomFromText('LINESTRING(115.97... 继续阅读 >