分类：PostgreSQL

2021
01-09

postgresql insert into select无法使用并行查询的解决

本文信息基于PG13.1。从PG9.6开始支持并行查询。PG11开始支持CREATETABLE…AS、SELECTINTO以及CREATEMATERIALIZEDVIEW的并行查询。先说结论：换用createtableas或者selectinto或者导入导出。首先跟踪如下查询语句的执行计划：selectcount(*)fromtestt1,test1t2wheret1.id=t2.id;postgres=#explainanalyzeselectcount(*)fromtestt1,test1t2wheret1.id=t2.id;... 继续阅读 >
2021
01-08

postgreSQL 使用timestamp转成date格式

尝试了以下两种方式，将pg中的timestamp格式转换成date格式：方式一：selectto_date(to_char(f.begin_time,'yyyy-mm-dd'),'yyyy-mm-dd')fromhafdf方式二：selectf.begin_time::DATEfromhafdf大概比较了一下，9万条测试数据，方式二的性能更好！补充：PostgreSQL中的时间戳格式转化常识前提：当数据库中保存的是timestamp类型时，我们需要通过这个时间戳来做乐观数据锁，那么久需要Select出来，然后在更新的时候在Upd... 继续阅读 >
2021
01-08

postgresql varchar字段regexp_replace正则替换操作

1.替换目标1）.contact字段类型varchar。2）.去掉字段中连续的两个，每个等号后面数字不同，effective_caller_id_name=051066824513,effective_caller_id_number=0510668245132.查询原字段内容selectcontactfrompbx_agentswherecontact~'effective_caller_id_name='limit2"{sip_append_audio_sdp=a=fmtp:18annexb=no,call_timeout=60,effective_caller_id_name=051066824513,effective_caller_id_number=051066824513,cti... 继续阅读 >
2021
01-08

关于PostgreSQL错误日志与慢查询日志收集

PostgreSQL错误日志与慢查询日志对于线上系统分析、问题预警、问题排查起到非常重要的作用，在此不做赘述。此文档记录错误日志与慢查询日志的收集、分析与存储展示的方法。一、总体思路PostgreSQL日志输出可以配置多种多样的格式，其中以csvlog格式输出的日志信息最全面。但是CSV日志只能以本地文件的方式收集，不能直接写入网络，实时上传日志服务器。日志收集：PostgreSQL服务器分布在不同的机器，我们使用rsyslog客户端-服务器... 继续阅读 >
2021
01-08

PostgreSQL 正则表达式替换-使用变量方式

###不定期更新把AAAA替换为A-A-A-A-javascriptalert('AAAA'.replace(/([A]{1})/g,"$1-"));()中的内容用变量$1$2$n代替PostgreSQLselectregexp_replace('AAAAAAAAAAAAAAAAAAAAAA','([A-Z]{1})','\1-','g')()中的内容用变量\1\2\n代替获取大括号中的内容selectf1[1]fromregexp_matches('asdfadfadsf{_id}','[\{]{1}(.*?)[\}]{1}')asf1字符串去重--\1表示只匹配第一个子串selectregexp_replace('adsfjjbkk中中','(.)... 继续阅读 >
2021
01-08

解决postgresql表中的字段名称包含特殊符号的问题

在创建postgresql表的时候，由于字段中包含了"()"这种特殊字符，导致创建表的时候报错。解决方法：给包含特殊字符的字段加上双引号""，再进行创建表即可例如：createtablefreighttransport_tab(idserial,日期TEXT,"重量(公斤)"TEXT)此时"重量(公斤)"字段包含特殊符号(),创建表时，在sql语句中对该字段添加双引号即可。补充：PostgreSQL去除字符串中的不可见字符终极方法测试表中数据如下：postgres=#select*fromte... 继续阅读 >
2021
01-08

PostgreSQL 实现查询表字段信息SQL脚本

PostgreSQL 实现查询表字段信息SQL脚本 查询格式：SELECTc.relname表名称,A.attnameAS字段名称,col_description(A.attrelid,A.attnum)AS注释,format_type(A.atttypid,A.atttypmod)AS类型,CASEWHENA.attnotnull='f'THEN'否'ELSE'是'ENDAS是否必填,a.attnum序号FROMpg_classASc,pg_attributeASaWHEREA.attrelid=C.oidANDA.attnum>0ORDERBYc.relname,a.attnum;查询示例：SELECTc.relname表名称,A.attnameAS字段名称,spl... 继续阅读 >
2021
01-08

PostgreSQL 定义返回表函数的操作

PostgreSQL 定义返回表函数的操作 本文我们学习如何在PostgreSQL开发返回表函数。示例数据表我们使用的示例数据库表为film，如下图所示：示例1第一个函数发挥所有满足条件film表记录，这里使用ilike操作，和like类似，但不区分大小写：CREATEORREPLACEFUNCTIONget_film(p_patternVARCHAR)RETURNSTABLE(film_titleVARCHAR,film_release_yearINT)AS$$BEGINRETURNQUERYSELECTtitle,cast(release_yearasinteger)FROMfilmWHEREtitleILIKE... 继续阅读 >
2021
01-08

对Postgresql中的json和array使用介绍

对Postgresql中的json和array使用介绍 结合近期接触到的知识点，做了一个归纳。会持续更新json官网文档http://www.postgres.cn/docs/12/datatype-json.htmljson的两种格式总结：json输入快，处理慢。是精准拷贝，所以能准确存储遗留对象的原格式，如对象键顺序。jsonb输入慢，处理快。会被重新解析成json数据，不保存原对象的键顺序，并且去重相同的键值，以最后一个为准。通常，除非有特别特殊的需要（例如遗留的对象键顺序假设），大多数应用应该更愿意把JSON数据... 继续阅读 >
2021
01-08

如何获取PostgreSQL数据库中的JSON值

在PostgreSQL数据库中有一列为JSON,要获取JSON中得数据可以用下面sql：selectordernoasOrderNo,amountasAmount,ordertimeasOrderTime,recordtypeasRecordTypefromjsonb_to_recordset((--特定方法selectarray_to_json(array_agg(data))--转换成一个数组fromwallet_detailswhereid=@id)::jsonb)asx(ordernotext,amountnumeric(16,6),ordertimetext,recordtypevarchar(32));如果你获取得数据... 继续阅读 >
2021
01-08

PostgreSQL 更新JSON,JSONB字段的操作

PostgreSQL 更新JSON,JSONB字段的操作 直接使用update表名set列名=(jsonb_set(列名::jsonb,'{key}','"value"'::jsonb))where条件要注意里面的单引号和双引号。补充：向PostgreSQL中json中加入某个字段或者更新某个字段的SQL语句需求：通过SQL的方式，对JSON里面的某个字段统一处理，更新成一个新值1、向PostgreSQL中json中加入某个字段。例如：向users表中id为3的data列中加入{“uptate_data”:“7”}执行SQL:updateuserssetdata='{"uptate_data":"7"}... 继续阅读 >
2021
01-08

postgreSQL数据库的监控及数据维护操作

postgreSQL数据库的监控及数据维护操作 目前postgreSQL数据库的管理，数据查询等都需要安装postgreSQL软件或安装pgadmin等，远程访问都需要先登录到服务器等繁琐的操作。如果是开发团队，那么每个开发，测试，管理人员都要经历这个麻烦的过程。使用Treesoft数据库管理系统，基于web方式管理postgreSQL,只需安装一次，大家直接通过浏览器使用，维护及监控数据库，大大简化工作量。监控的指标包括：连接数，数据库大小，表空间，锁情况，QPS事务数等。Treesoft数据库管理系... 继续阅读 >
2021
01-08

MySQL用户管理与PostgreSQL用户管理的区别说明

一.MySQL用户管理【例1.1】使用root用户登录到本地mysql服务器的test库中mysql-uroot-p-hlocalhosttest【例1.2】使用root用户登录到本地mysql服务器的test库中，执行一条查询语句mysql-uroot-p-hlocalhosttest-e"DESCperson;"【例1.3】使用CREATEUSER创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhostCREATEUSER'jeffrey'@'localhost'IDENTIFIEDBY'mypass';【例1.4】使用GRANT语句创建一个新的用... 继续阅读 >
2021
01-08

PostgreSQL数据库中匿名块的写法实例

PostgreSQL数据库中匿名块的写法实例 看代码吧~test=#DO$$DECLAREirecord;test$#BEGINtest$#FORiIN1..10test$#LOOPtest$#execute'selectloop_insert(1)';test$#ENDLOOP;test$#END$$;DOtest=#看匿名块的执行效果：test=#selectcount(*)fromlineitem;count-------7000(1row)test=#selectcount(*)fromlineitem;count-------17000------------->>>>匿名块插入了10000条记录.(1row)test=#补充：PostgreSql的PL/pgSQL块结构（在pgA... 继续阅读 >
2021
01-08

postgresql 查看当前用户名的实现

postgresql 查看当前用户名的实现 查看当前连接的用户名：foo=#select*fromcurrent_user;或foo=#selectuser;查看所有用户名：foo=#\du补充：postgres用户管理首次安装完成pg数据库后，会默认自带一个用户，用户名：postgres密码：postgres可以使用命令\du查看数据库用户创建新用户：createuserdevwithpassword'******';创建完成后，使用如下语句登录：psql-Udev-W以上为个人经验，希望能给大家一个参考，也希望大家多多支持自学编程网。如有错误... 继续阅读 >