分类：Postgresql

2021
01-29

Postgresql 数据库转义字符操作

产生问题Postgresql数据库运行下面insert命令insertintomapping_mac_brand(_id,mac,brand)values(777,'D86595','Toy\'sMythInc.')提示下面的警告：Warning:nonstandarduseof\'inastringliteralHINT:Use''towritequotesinstringsorusetheescapestringsyntax(E...')解决办法1.名词解释stringliteral：字符串字面量escapestring：转义字符串escapecharacter：转义字符EscapeSequence：转义字符串转义... 继续阅读 >
2021
01-29

postgresql~*符号的含义及用法说明

操作符~~等效于LIKE，而~~*对应ILIKE。还有!~~和!~~*操作符分别代表NOTLIKE和NOTILIKE。另外：~匹配正则表达式，大小写相关'thomas'~'.*thomas.*'~*搜索匹配正则表达式，大小写无关'thomas'~*'.*Thomas.*'!~不匹配正则表达式，大小写相关'thomas'!~'.*Thomas.*'!~*不匹配正则表达式，大小写无关'thomas'!~*'.*vadim.*'补充：Postgresql字符串检索关键是和符号LIKE,ESCAPE,ILIKE,~,~*,!~,... 继续阅读 >
2021
01-29

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

按照日期查询通常有好几种方法：按照日期范围查询有好几种方法，日期字段类型一般为：Timestampwithouttimezone方法一：select*fromuser_infowherecreate_date>='2015-07-01'andcreate_date<'2015-08-15';方法二：select*fromuser_infowherecreate_datebetween'2015-07-01'and'2015-08-15';方法三：select*fromuser_infowherecreate_date>='2015-07-01'::timestampandcreate_date<'2015-08-15'::tim... 继续阅读 >
2021
01-29

postgresql 实现将数组变为行

postgresql 实现将数组变为行 有的时候需要把数组元素同表中的字段进行关系运算，首先得把array变为记录行SELECT"unnest"(array[1,2,3])结果：unnest求数组交集：SELECT"unnest"(array[1,2,3])INTERSECTSELECT"unnest"(array[3,4,5])结果：unnest3补充：PostgreSQL单列多行变一行&一行变多行工作中经常遇到这样一个场景，希望将某个字断查询出得结果组合成为一个字符串，用逗号分割（或者分号），通过PG中的函数该如何实现？多行变一行实例表：想要的结果... 继续阅读 >
2021
01-29

PostgreSQL 对数组的遍历操作

PostgreSQL 对数组的遍历操作 PostgreSQL提供了数组类型。我来演示下如何具体使用创建一个有数组类型字段的表。createtabletest_array(idserialprimarykey,str1int[][][]);插入两条测试数据。insertintotest_arrayvalues(1,array[[[1,2],[3,4],[5,6]],[[20,30],[40,50],[70,100]]]);insertintotest_arrayvalues(2,array[[[100,200],[300,400],[500,600]],[[2000,3000],[4000,5000],[7000,10000]]]);为了能直观的看到结果集，我们得把数组的值换... 继续阅读 >
2021
01-29

PostgreSQL存储过程循环调用方式

需求描述碰到需求,需要往表里插入5万条数据,打算使用存储过程,但是postgres数据库没有建存储过程的SQL,所以使用函数来实现.表数据结构完整性要求一次插入两条记录,两条记录相互外键约束,record1的partner_id字段值是record2的主键id的值,record2的partner_id字段值是record1的主键id的值.实现createorreplacefunctioncreatData()returnsbooleanas$BODY$declareiiinteger;declareid1integer;declareid... 继续阅读 >
2021
01-29

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

一、假设有表student，字段分别有id,remark,name等字段。二、写一个存储函数，根据传过去的变量ID更新remark的内容。调用该存储函数格式如下：selectupdate_student(1);三、存储函数示例如下：CREATEORREPLACEFUNCTIONpublic.update_student(idinteger)RETURNStextAS$BODY$declaresql_str_runtext;BEGIN/*--method1select'updatestudentsetremark='''||now()||'''wherestudent.id='||$1intosql_str_ru... 继续阅读 >
2021
01-29

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

postgresql 导入数据库表并重设自增属性的操作 postgresql使用Navicat软件导出数据库表，在导入会数据库的操作。postgresql的自增字段是通过序列sequence来实现的。1、先删除导出的数据库表中的自增属性2、导入数据库表之后，需要创建序列。注：一般序列名称由数据表名+主键字段+seq组成（通常情况下主键字段即为自增字段），如下表名为cof_table_hot_analysis，主键字段为idCREATESEQUENCEcof_table_hot_analysis_id_seqSTARTWITH1INCREMENTBY1NOMINVALUE... 继续阅读 >
2021
01-29

postgresql coalesce函数数据转换方式

oracle的NVL(col,0)是判断如果col字段为空的时候赋值0，同样的postgresql中的函数就是coalesce，用法是一样的SELECTcoalesce(collect_result,value)ascollect_resultFROMcollect数据库中如果查询的字段collect_result为空那么赋值value给collect_result。补充：postgresqlcoalesce字段为空时赋一个固定值,等同oracle的nvl字段为空时赋一个固定值selectcoalesce(column_name,0)fromtable_name;以上为个人经验，希望... 继续阅读 >
2021
01-29

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

场景：存在一个用户白名单表，提供了此用户的用户名和地区信息，判断此用户是否在此白名单表中。如：姓名白名单地区张三中国，美国则可使用如下语句：SELECTID,user,area_listFROMt_white_userWHEREuser=#{user,jdbcType=VARCHAR}AND(COALESCE(area_list,'')LIKECONCAT('%',#{country,jdbcType=VARCHAR},'%')ORarea_listISNULL)LIMIT1技巧点分析：1、coalesce函数说明：返回其... 继续阅读 >
2021
01-29

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

我就废话不多说了，大家还是直接看代码吧~UPDATEtablenameSETtags=jsonb_set(tags-'landuse_area','{landuse_area}',('"'||round((ST_Area(ST_Transform(geom,4527))*0.0015)::NUMERIC,3)||'"')::jsonb,TRUE)WHEREtags@>'{"name":"张三"}';round的目的是保留3位小数st_area是计算多边形的面积，后面的0.0015是面积单位转换为亩st_transform是投影转换，这里我表中原始的geom是4326，不适合计算面积，所以在这里... 继续阅读 >
2021
01-29

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

1、null表示缺失的值,''""表示空值null参与的表达式运算都返回null使用isnull判断是nullisnotnull判断非null2、nullif(value1,value2)如果value1==value2返回null3、coalesce(arg1,arg2,...)返回第一个不为null的值所以可以使用如下语句，实现将table中filed为空的记录替换为指定值updatetablesetfiled=COALESCE(NULLIF(trim("filed"),''),'value')COALESCE(NULLIF(to_char(T1.online_tm,'yyyy-MM-ddH... 继续阅读 >
2021
01-29

PostgreSQL 禁用全表扫描的实现

PostgreSQL可以通过一些设置来禁用全表扫描（FULLSCAN/SeqScan）注意：设置此功能后不是完全避免全表扫描，而是只要有不通过全表扫描能得出结果的就不走全表扫描。如果什么路都不通，那肯定得全表扫描，不然怎么获取数据。而且并不是不走全表扫描性能就一定好。下面展示下这个功能：查询表结构：highgo=#\dtestTabletestColumn|Type|Modifiers-------------+--------------------------------+-----------G... 继续阅读 >
2021
01-29

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

在PostgreSQL中可以使用Array数据结构，例如selectarray[1,2,3];return{1,2,3}但是，如果selectarray[1,2,3][1];--会报错select(selectarray[1,2,3])[1]--可以使用那么在用正则匹配函数regexp_match就会遇到这样的问题，如selectregexp_match('123-123','(\d+)-(\d+)');--return{123,123}selectregexp_match('123-123','(\d+)-(\d+)')[1];--报错但是，如果你想获取其中一个元素，你就得使用嵌套查询，如select... 继续阅读 >
2021
01-29

postgresql 中的 like 查询优化方案

当时数量量比较庞大的时候，做模糊查询效率很慢，为了优化查询效率，尝试如下方法做效率对比一、对比情况说明：1、数据量100w条数据2、执行sql二、对比结果explainanalyzeSELECTc_patent,c_applyissno,d_applyissdate,d_applydate,c_patenttype_dimn,c_newlawstatus,c_abstractFROMpublic.t_knowl_patent_zlxx_tempWHEREc_applicantLIKE'%本溪满族自治县连山关镇安平安养殖场%';1、未建索时执行计划："Gather(cost... 继续阅读 >
2021
01-29

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

我就废话不多说了，大家还是直接看代码吧~改前：select*from表名where字段名like||#{参数}||'%'改后：select*,regexp_matches(字段名,#{参数})from表名补充：postgresql实现模糊查询正则表达式因为数据库的查询操作比较单一，所以大部分的模糊查询操作都需要手动编写程序来实现。postgresql提供有强大的正则表达式系统，可以在数据库级别实现模糊查询。正则表达式匹配操作符：操作符描述例子... 继续阅读 >