108浏览

0评论

解决postgresql 数据库 update更新慢的原因

;大约140000条数据） 竟然运行了一个小时还没有完成
下面是我的几点解决方案
我的update 语句 是从一个临时表更新值到另一个正式表
因为具体数据需要保密，我就不截图了 只说说...

PostgreSQL
• 2021-01-28

97浏览

0评论

postgresql数据库使用说明_实现时间范围查询

按照日期查询通常有好几种方法：按照日期范围查询有好几种方法，日期字段类型一般为：Timestamp without timezone方法一：select * from user_info where create_date>= '2015-07-0...

PostgreSQL
• 2021-01-28

96浏览

0评论

postgresql 实现将数组变为行

有的时候需要把数组元素同表中的字段进行关系运算，首先得把array变为记录行SELECT "unnest"(array[1,2,3])结果：unnest求数组交集：SELECT "unnest"(array[1,2,3]) INTERSECT SE...

PostgreSQL
• 2021-01-28

193浏览

0评论

postgresql 存储函数调用变量的3种方法小结

一、假设有表student，字段分别有id,remark,name等字段。二、写一个存储函数，根据传过去的变量ID更新remark的内容。调用该存储函数格式如下：select update_student(1);三、存储...

PostgreSQL
• 2021-01-27

188浏览

0评论

postgresql 导入数据库表并重设自增属性的操作

postgresql使用Navicat软件导出数据库表，在导入会数据库的操作。postgresql 的自增字段 是通过 序列 sequence来实现的。1、先删除导出的数据库表中的自增属性2、导入数据库...

PostgreSQL
• 2021-01-27

133浏览

0评论

postgresql coalesce函数数据转换方式

oracle 的NVL(col,0)是判断如果col字段为空的时候赋值0，同样的postgresql中的函数就是coalesce，用法是一样的 SELECT coalesce(collect_result,value) as collect_result...

PostgreSQL
• 2021-01-27

124浏览

0评论

解决PostgreSQL Array使用中的一些小问题

在PostgreSQL 中可以使用Array数据结构，例如select array[1,2,3]; return {1,2,3}但是，如果select array[1,2,3][1]; --会报错 select (select array[1,2,3])[1] --可以使用那...

PostgreSQL
• 2021-01-27

226浏览

0评论

PostgreSQL 禁用全表扫描的实现

PostgreSQL可以通过一些设置来禁用全表扫描（FULL SCAN/Seq Scan）注意：设置此功能后不是完全避免全表扫描，而是只要有不通过全表扫描能得出结果的就不走全表扫描。如果什么路都不...

PostgreSQL
• 2021-01-27

182浏览

0评论

postgresql 实现将字段为空的值替换为指定值

1、null 表示缺失的值, '' " "表示空值null 参与的表达式运算都返回null使用is null 判断是nullis not null 判断非null2、nullif(value1, value2) 如果value1 == value2 返...

PostgreSQL
• 2021-01-27

174浏览

0评论

postgresql 实现修改jsonb字段中的某一个值

我就废话不多说了，大家还是直接看代码吧~UPDATE tablename SET tags = jsonb_set(tags-'landuse_area', '{landuse_area}',('"' || round((ST_Area(ST_Transform(geom,4527))...

PostgreSQL
• 2021-01-27

426浏览

0评论

postgresql 中的COALESCE()函数使用小技巧

场景：存在一个用户白名单表，提供了此用户的用户名和地区信息，判断此用户是否在此白名单表中。如：姓名 白名单地区张三 中国，美国则可使用如下语句：SELECT ID, user, area_listF...

PostgreSQL
• 2021-01-27

196浏览

0评论

postgresql 实现replace into功能的代码

PostgreSQL 9.5-使用函数或with实现create table test(id int primary key, info text, crt_time timestamp);with upsert as (update test set info='test',crt_time=now()...

PostgreSQL
• 2021-01-27

297浏览

0评论

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

我就废话不多说了，大家还是直接看代码吧~UPDATE table set name = trim(name);//用来删除数据前后的空格 UPDATE table set name = rtrim(name);//用来删除数据前的空格 UPDA...

PostgreSQL
• 2021-01-27

110浏览

0评论

PostgreSQL 远程连接配置操作

postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。打开安装目录下的data文件，我一般安装在D盘，我安装的postgre是64位，所以，安装目录为D...

PostgreSQL
• 2021-01-27

128浏览

0评论

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

测试数据：city表：create table city(id int,name text);insert into city values(0,'北京'),(1,'西安'),(2,'天津'),(3,'上海'),(4,'哈尔滨'),(5,'西藏')person表：create table...

PostgreSQL
• 2021-01-27

143浏览

0评论

PostgreSQL regexp_matches替换like模糊查询的操作

我就废话不多说了，大家还是直接看代码吧~改前：select * from 表名 where 字段名 like ||#{参数}||'%'改后：select *,regexp_matches(字段名, #{参数}) from 表名补充：postgresql...

PostgreSQL
• 2021-01-27

76浏览

0评论

浅谈pg_hint_plan定制执行计划

有的时候PG给出的执行计划由于很多原因并不是最优的，需要手动指定执行路径时我们可以加载pg_hint_plan这个插件。1 安装插件预先安装Postgresql10.7cd postgresql-10.7/contr...

PostgreSQL
• 2021-01-26

161浏览

0评论

PostgreSQL 慢查询SQL跟踪操作

PostgreSQL 开启慢SQL捕获在排查问题时是个很有效的手段。根据慢SQL让我在工作中真正解决了实际问题，很有帮助。PostgreSQL 日志支持的输出格式有 stderr（默认）、csvlog 、sysl...

PostgreSQL
• 2021-01-26

149浏览

0评论

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

主从复制1. 基于文件的日志传送创建一个高可用性（HA）集群配置可采用连续归档，集群中主服务器工作在连续归档模式下，备服务器工作在连续恢复模式下（1台或多台可随时接管主服务器），备...

PostgreSQL
• 2021-01-26

228浏览

0评论

PostgreSQL 修改视图的操作

最近发现PostgreSQL（create or replace） 修改视图有很多的限制，不像SQL Server和Oracle那样可以随意修改。错误提示修改视图原有字段ERROR: cannot change name of view column...

PostgreSQL
• 2021-01-26

112浏览

0评论

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

项目最早是基于Oracle的，移植到PostgreSQL后，本着尽量少修改的原则，创建/更新视图的脚本也沿用了Oracle风格的CREATE OR REPLACE VIEW形式。但是每当要更新视图定义时，常常报"ca...

PostgreSQL
• 2021-01-26

164浏览

0评论

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

我就废话不多说了，大家还是直接看代码吧~select count(s.*)from ( select *, row_number() over (partition by fee_date order by fee_date) as gr from new_order where...

PostgreSQL
• 2021-01-26

139浏览

0评论

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

实例解析：select to_char(log.date, 'yyyy-MM-dd HH24') as hour, log.exten, sum(case log.grade when '1' then 1 else 0 end) as "1", sum(case log.grade when '2' the...

PostgreSQL
• 2021-01-26

140浏览

0评论

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

postgresql计算两点距离下面两种方法：select ST_Distance( ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography, ST_SetSRID(ST_MakePoint(1...

PostgreSQL
• 2021-01-26

135浏览

0评论

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

之前用的是ST_Distance 函数，但是貌似需要进行一次单位的转换，而且网上有说那种转换不是特别准确，现在暂时将该算法记录在此：select st_distance(ST_GeomFromText('POINT(120.45...

PostgreSQL
• 2021-01-26

362浏览

0评论

postgresql 除法保留小数位的实例

我就废话不多说了，大家还是直接看代码吧~\xselect 8/(100-3) as c1, round(8/(100-3) ,4) as c2, round(8/(100-3)::numeric ,4) as c3, 8/(100-3)::numeric as c4;-[ RECORD...

PostgreSQL
• 2021-01-26

148浏览

0评论

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

大家好！我是只谈技术不剪发的 Tony 老师。今天我们来聊聊 PostgreSQL 的性能优化；数据库优化是一个系统的工程，本文只专注于服务器的参数配置优化。默认安装时，PostgreSQL 的配...

PostgreSQL
• 2021-01-26

106浏览

0评论

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

对于庞大的数据，检索sql的编写要格外小心，有很多平时不注意的sql可能就会变成瓶颈。比如, 我们有个系统, 其中t96_pd_log表，记录数8000w多，在开发阶段乃至用了那么多年都没问题,...

PostgreSQL
• 2021-01-26

136浏览

0评论

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

我就废话不多说了，大家还是直接看代码吧~SELECT tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS...

PostgreSQL
• 2021-01-26

219浏览

0评论

Visual Studio Code(VS Code)查询PostgreSQL拓展安装教程图解

1.打开VS Code拓展，搜索postgresql，这时就可以看到在线的支持postgresql的插件，这里我选择的第一个插件（下载量最多的）；2.安装postgresql插件，安装完成之后，在左侧就可以看到插件图...

PostgreSQL
• 2021-01-22

276浏览

0评论

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

作为一个表达式使用的子查询返回了多列：在查询中，我们需要以第2条查询语句作为第一条查询语句的条件，但是第一条根据这个条件查询出来是多个数据，这时候又需要保留多个数据，运用...

PostgreSQL
• 2021-01-22

82浏览

0评论

postgresql模糊匹配大杀器(推荐)

ArteryBase-模糊匹配大杀器问题背景随着pg越来越强大，abase目前已经升级到5.0(postgresql10.4)，目前abase5.0继承了全文检索插件(zhparser)，使用全文检索越来越方便。本文会对a...

PostgreSQL
• 2021-01-22

114浏览

0评论

PostgreSQL利用递归优化求稀疏列唯一值的方法

在数据库中经常会碰到一些表的列是稀疏列，只有很少的值，例如性别字段，一般就只有2种不同的值。
但是当我们求这些稀疏列的唯一值时，如果表的数据量很大，速度还是会很慢。例如：
创...

PostgreSQL
• 2021-01-20

271浏览

0评论

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

今天遇到一个问题 之前用TimescaleDB创建的超表 是已7天为单位的 由于7天的数据量过大 影响查询效率 需要把7天的分区改为一天一分区创建超表 create_hypertable（）1.创建普通...

PostgreSQL
• 2021-01-20

174浏览

0评论

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

在postgresql中，设置已存在的某列(num)值自增，可以用以下方法：//将表tb按name排序，利用row_number() over()查询序号并将该列命名为rownum，创建新表tb1并将结果保存到该表中creat...

PostgreSQL
• 2021-01-19

131浏览

0评论

基于PostgreSQL密码重置操作

今天我在用命令行登陆postgresql的时候，忽然忘记密码了，重试了N次还是不行，然后我就试着上网找解决之道，最后完美的解决了，可是后来我才发现还有个更简单的方法，该篇博客用来记录...

PostgreSQL
• 2021-01-19

124浏览

0评论

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

PostgreSQL登录1、可通过客户端pgAdmin III直接登录2、可通过命令行命令：psql -h 10.10.10.10 -U user -d postgres -p 5570-h：数据库IP-U：登录用户-d：登录的数据库-p：登录端口方...

PostgreSQL
• 2021-01-19

249浏览

0评论

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

在UNIX平台中安装PostgreSQL之后，PostgreSQL会在UNIX系统中创建一个名为“postgres”当用户。PostgreSQL的默认用户名和数据库也是“postgres”，不过没有默认密码。在安装Post...

PostgreSQL
• 2021-01-19

281浏览

0评论

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

近期测试了一下postgresql的加密扩展插件pgcrypto的aes加密安装加密扩展插件：pgcrypto在主节点上安装create extension pgcrypto;aes加解密函数简单介绍encrypt(data bytea,...

PostgreSQL
• 2021-01-19

220浏览

0评论

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

膨胀率的精确计算PostgreSQL自带了pgstattuple模块，可用于精确计算表的膨胀率。譬如这里的tuple_percent字段就是元组实际字节占关系总大小的百分比，用1减去该值即为膨胀率。#...

PostgreSQL
• 2021-01-19

323浏览

0评论

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

索引膨胀的几个来源：1 大量删除发生后，导致索引页面稀疏，降低了索引使用效率。2 PostgresQL 9.0之前的版本，vacuum full 会同样导致索引页面稀疏。3 长时间运行的事务，禁止vacuum...

PostgreSQL
• 2021-01-19

222浏览

0评论

PostgreSQL的外部数据封装器fdw用法

数据封装器fdw（Foreign Data Wrappers）在PostgreSQL中相当于oracle中的dblink，可以很方便的操作其他数据库中的数据。场景，在本地的test库中通过外部数据封装器fdw访问本地的tes...

PostgreSQL
• 2021-01-19

202浏览

0评论

PostgreSQL 中的postgres_fdw扩展详解

通过postgres_fdw 扩展，访问远程数据库表一、环境准备虚拟机(node107)：centos7、PostgreSQL10远程服务器(百度云服务BBC)： centos7、PostgreSQL10在本地虚拟机上访问远程服务器...

PostgreSQL
• 2021-01-19

135浏览

0评论

PostgreSQL的B-tree索引用法详解

结构B-tree索引适合用于存储排序的数据。对于这种数据类型需要定义大于、大于等于、小于、小于等于操作符。通常情况下，B-tree的索引记录存储在数据页中。叶子页中的记录包含...

PostgreSQL
• 2021-01-18

151浏览

0评论

Postgresql 如何选择正确的关闭模式

停止数据库的命令：pg_ctl stop -D $PGDATA [-m shutdown-mode]shutdown-mode有如下几种模式：1. smart: 等所有的连接中止后，关闭数据库。如果客户端连接不终止， 则无法关闭数据...

PostgreSQL
• 2021-01-18

255浏览

0评论

postgresql 如何关闭自动提交

postgresql中默认是自动提交的查看是否是自动提交： postgres=# \echo :AUTOCOMMIT on关闭自动提交： postgres=# \set AUTOCOMMIT off postgres=# \echo :AUTOCOMMIT off另...

PostgreSQL
• 2021-01-18

544浏览

0评论

Postgresql 解决pg掉电后无法重启的问题

机房断电后重启pg数据库，报如下错误：< 2019-01-01 22:42:59.240 EST >LOG: database system was interrupted while in recovery at 2019-01-01 22:00:58 EST< 2019-01-01 22:...

PostgreSQL
• 2021-01-18

139浏览

0评论

PostgreSQL 恢复误删数据的操作

在Oracle中；删除表或者误删表记录；有个闪回特性，不需要停机操作，可以完美找回记录。当然也有一些其他的恢复工具；例如odu工具，gdul工具。都可以找回数据。而PostgreSQL目前没有闪...

PostgreSQL
• 2021-01-18

224浏览

0评论

PostgreSQL 使用raise函数打印字符串

raise函数在PostgreSQL中，该函数用于打印字符串，类似于Java中的System.out.println()，Oracle中的dbms_output.put_line()。用法如下：raise notice 'My name is %, I am a %.', '...

PostgreSQL
• 2021-01-18

190浏览

0评论

PostgreSQL 打印日志信息所在的源文件和行数的实例

一直好奇在PG中, 当输出错误日志时, 如何能够附带错误信息所在的源代码文件名以及发生错误的代码行数.postgres.conf中, log信息冗余级别为"default(默认)", terse: 表示更...

PostgreSQL
• 2021-01-18