一、使用步骤
1、创建dblink扩展,连接与被连接的两个数据库都要执行下面sql
1
createextensionifnotexistsdblink;
2、跨库查询或增删改
#查询
SELECT
*
FROM
dblink('host=localhostport=5432dbname=piedssdb_sortuser=postgrespassword=qQq314159@26','selectuser_id,accountfrompiedss_biz.sys_user')AST(IDTEXT,NAMETEXT);
#新增
SELECTdblink_exec('host=localhostport=5432dbname=piedssdb_sortuser=postgrespassword=qQq314159@26','INSERTINTOpiedss_biz.sys_user(user_id,account,password,sex,super_admin_flag,status_flag,del_flag,create_user,real_name)VALUES(''1588006895019589631'',''ericfrq'',''1qazWSX'',''F'',''Y'',''1'',''N'',''dms_datahub'',''管理员'')');
#将库A的数据查询出来后直接插入存库B
INSERTINTOdms_usercenter_userinfo(ID,true_name,username,PASSWORD,phone)SELECT
*
FROM
dblink('host=localhostport=5432dbname=piedssdb_sortuser=postgrespassword=qQq314159@26','SELECTsys_user.user_id,sys_user.real_name,sys_user.account,sys_user.password,sys_user.telFROMpiedss_biz.sys_user')AST(IDTEXT,true_nameTEXT,usernameTEXT,PASSWORDTEXT,phoneTEXT);
#修改
SELECTdblink_exec('host=localhostport=5432dbname=piedssdb_sortuser=postgrespassword=qQq314159@26','UPDATEpiedss_biz.sys_userSETaccount=''ericfrq'',password=''1qazWSX'',sex=''F'',super_admin_flag=''Y'',status_flag=''1'',del_flag=''N'',create_user=''dms_datahub'',real_name=''管理员''WHEREuser_id=''158800689501958963111''');
#删除
SELECTdblink_exec('host=localhostport=5432dbname=piedssdb_sortuser=postgrespassword=qQq314159@26','DELETEFROMpiedss_biz.sys_userWHEREuser_id=''4028db8283d486350183d533f7570000''ANDcreate_user=''dms_datahub''');
3、如果不想每一次都写完整的dblink连接信息,可以先起别名
4
5
6
#起别名
selectdblink_connect('bieming','host=localhostport=5432dbname=piedssdb_sortuser=postgrespassword=qQq314159@26');
#进行操作
SELECTdblink_exec('bieming','INSERTINTOpiedss_biz.sys_user(user_id,account,password,sex,super_admin_flag,status_flag,del_flag,create_user,real_name)VALUES(''1588006895019589631'',''ericfrq'',''1qazWSX'',''F'',''Y'',''1'',''N'',''dms_datahub'',''管理员'')');
#关闭连接
SELECTdblink_disconnect('bieming');
4、补充:mybatis直接执行上面的sql写法
参考下面补充介绍:pgsql个人笔记,mybatis+postgresql写原生sql,不用xml
补充:pgsql个人笔记
一、mybatis+pgsql的xml
下面统计的sql中用到的聚合函数具体解析说明:第一部分
array_to_string(ARRAY_AGG(stp.source_server),','):将stp的source_server的数据转化为数组,再以逗号分隔拼接起来转成字符串。
array_to_string(ARRAY_AGG(stp.target_server),',')将stp的target_server的数据转化为数组,再将数组转换为字符串,用“,”分隔。(有点类似于Mysql的group_concat()函数)
concat_ws(',','a','b'):将ab以逗号连接。在下面的案例中:concat_ws(',',array_to_string(ARRAY_AGG(stp.source_server),','),array_to_string(ARRAY_AGG(stp.target_server),','))是将第一步的两个结果,合并成一个字符串
regexp_split_to_table((a,b),','):将a,b以逗号分隔开并将a、b分别作为表查询的结果。在下面的案例中,regexp_split_to_table((concat_ws(',',array_to_string(ARRAY_AGG(stp.source_server),','),array_to_string(ARRAY_AGG(stp.target_server),','))),',')将第二步的结果,以逗号“,”分隔,并把每一项作为结果
DISTINCT将第三步的结果去重
COUNT(*)统计第四步去重后的数量
第二部分
SUM(stad.data_volume),计算data_volume的和
ROUND('100'::NUMERIC/10,3)将100除以10后保留小数点后三位。其中::NUMERIC将字符串’100’转为数字(numeric类型最多能存储有1000个数字位的数字并且能进行准确的数值计算。它主要用于需要准确地表示数字的场合,如货币金额。不过,对numeric类型进行算术运算比整数类型和浮点类型要慢很多。)。案例中ROUND(SUM(stad.data_volume)::NUMERIC/(1024*1024*1024),3)将第一步的结果转为字符串并除以1024的三次方(将字节B转–>kb–>mb–>GB)
CAST(oti.institution_idASVARCHAR)将int型institution_id转为varchar型
将时间字段格式化为指定格式to_char(create_time,'yyyy-mm-dd')
mybatis+postgresql写原生sql,不用xml
@Select({"${sqlStr}"})
@Results({
@Result(column="gid",property="gid",jdbcType=JdbcType.INTEGER,id=true),
@Result(column="name",property="name",jdbcType=JdbcType.VARCHAR),
@Result(column="geom",property="geom",jdbcType=JdbcType.VARCHAR),
@Result(column="code",property="code",jdbcType=JdbcType.VARCHAR)
})
List<ModelPolygon>exeNativeSql(@Param("sqlStr")StringsqlStr);
@Select({"${sqlStr}"})
List<ModelPolygon>exeNativeSql(@Param("sqlStr")StringsqlStr);
//"selectgidasgid,nameasname,ST_AsGeoJson(geom)asgeom,codeascodefromwl_model_polygon"
整个dao层的写法:
packagecom.xxx.mapper;
importcom.alibaba.fastjson.JSONObject;
importcom.baomidou.mybatisplus.core.mapper.BaseMapper;
importcom.xxx.entity.UserInfo;
importorg.apache.ibatis.annotations.*;
importorg.apache.ibatis.type.JdbcType;
importjava.util.List;
@Mapper
publicinterfaceUserInfoMapperextendsBaseMapper<UserInfo>{
@Select({"${sqlStr}"})
@Results({
@Result(column="id",property="id",jdbcType=JdbcType.VARCHAR,id=true),
@Result(column="username",property="username",jdbcType=JdbcType.VARCHAR),
@Result(column="email",property="email",jdbcType=JdbcType.VARCHAR),
@Result(column="phone",property="phone",jdbcType=JdbcType.VARCHAR),
@Result(column="password",property="password",jdbcType=JdbcType.VARCHAR),
@Result(column="true_name",property="trueName",jdbcType=JdbcType.VARCHAR),
@Result(column="usetime",property="usetime",jdbcType=JdbcType.VARCHAR)
})
List<UserInfo>exeNativeQuerySql(@Param("sqlStr")StringsqlStr);
@Select({"${sqlStr}"})
List<JSONObject>exeNativeExecSql(@Param("sqlStr")StringsqlStr);
}
<!--数据量统计-->
<selectid="getDataByParams"resultType="com.htht.datatrans.app.vo.CountProtocolVO">
SELECT
*
FROM
(
SELECTCOUNT
(*)ASuseNode
FROM
(
SELECTDISTINCT
regexp_split_to_table(
(
concat_ws(',',array_to_string(ARRAY_AGG(stp.source_server),','),array_to_string(ARRAY_AGG(stp.target_server),','))
),
','
)
FROM
sync_t_protocolASstp
WHERE
stp.deleted=0
ANDstp.protocol_type='datacommunication'
)res
)node1,
(
SELECTCOUNT
(*)ASrunningNode
FROM
(
SELECTDISTINCT
regexp_split_to_table(
(
concat_ws(',',array_to_string(ARRAY_AGG(stp.source_server),','),array_to_string(ARRAY_AGG(stp.target_server),','))
),
','
)
FROM
sync_t_protocolASstp
WHERE
stp.deleted=0
ANDstp.protocol_type='datacommunication'
ANDstp.run_state='running'
)res
)node2,
(SELECTCOUNT(*)ASprotocolTotalFROMsync_t_protocolASstpWHEREstp.deleted=0ANDstp.protocol_type='datacommunication')protocol1,
(
SELECTCOUNT
(*)ASrunningProtocol
FROM
sync_t_protocolASstp
WHERE
stp.deleted=0
ANDstp.protocol_type='datacommunication'
ANDstp.run_state='running'
)protocol2,
(
SELECTCOUNT
(*)ASexceptionalProtocol
FROM
sync_t_protocolASstp
WHERE
stp.deleted=0
ANDstp.protocol_type='datacommunication'
ANDstp.run_state='exception'
)protocol3,
(
SELECT
ROUND(SUM(stad.data_volume)::NUMERIC/(1024*1024*1024),3)ASrunningData
FROM
sync_t_action_detailASstad
INNERJOINsync_t_protocolASstpONstad.protocol_id=stp.protocol_id
ANDstp.deleted=0
ANDprotocol_type='datacommunication'
WHERE
stad.execute_state=ANY(STRING_TO_ARRAY('running',','))
)data1,
(
SELECT
ROUND(SUM(stad.data_volume)::NUMERIC/(1024*1024*1024),3)AShistoryData
FROM
sync_t_action_detailASstad
INNERJOINsync_t_protocolASstpONstad.protocol_id=stp.protocol_id
ANDstp.deleted=0
ANDprotocol_type='datacommunication'
WHERE
stad.execute_state=ANY(STRING_TO_ARRAY('succeed,failed',','))
)data2
</select>
<select
id="getPagesByParams"
resultType="com.htht.datatrans.app.vo.CloudVO">
select*fromops_t_cloudwheredelete=0
<iftest="cloudProvider!=nullandcloudProvider!=''">
andcloud_providerlike'%'||#{cloudProvider,jdbcType=VARCHAR}||'%'
</if>
orderbycloud_id
</select>
<select
id="getByCloudCodes"
resultType="com.htht.datatrans.app.entity.Cloud">
select*fromops_t_cloudwheredelete=0
<iftest="cloudCodes!=nullandcloudCodes!=''">
andcloud_code=ANY(STRING_TO_ARRAY(#{cloudCodes,jdbcType=VARCHAR},','))
</if>
orderbycloud_id
</select>
<select
id="getPagesByParams"
resultType="org.springblade.modules.datatrans.vo.ServerPageVO">
selectots.*,otc.cloud_nameascloudName,otc.domain_nameasdomainName
fromops_t_serverots
innerjoinops_t_cloudotconots.cloud_id=otc.cloud_id
<iftest="institutionId!=nullandinstitutionId!=''">
innerjoinops_t_institutionotiONCAST(oti.institution_idASVARCHAR)=ots.institution_id
</if>
whereots.deleted=0
<iftest="cloudProvider!=nullandcloudProvider!=''">
andotc.cloud_providerlikeconcat(concat('%',#{cloudProvider,jdbcType=VARCHAR}),'%')
</if>
orderbyots.server_id
</select>
二、字符串替换
将address字段里的“区”替换为“呕”显示,如下
select*,replace(address,'区','呕')ASrep
fromtest_tb
将name字段里的“我”替换为“你”保存,如下
UPDATEblade_visual
SET"name"=(REPLACE(NAME,'你','你们三'))
三、postgre做空间数据分析
比如面相交
1、使用步骤新建空间索引createextensionpostgis;创建geometry类型字段
3.插入geometry数据
insertintowl_model_polygon(geom,name,code)values('SRID=4326;POLYGON((116.207885742187539.928694653732364,116.2092590332031239.91078961774283,116.2065124511718839.89393354266699,116.2339782714843639.86547951378614,116.2449645996093839.82752244475985,116.2985229492187639.78954439311165,116.339721679687539.78532331459258,116.383666992187539.78848914776114,116.4179992675781139.79904087286648,116.44409179687539.80748108746673,116.4591979980468839.818029898770206,116.4811706542968639.83490462943255,116.5031433105468839.86231722624386,116.5058898925781239.88023492849342,116.504516601562539.90973623453719,116.493530273437539.925535281697286,116.504516601562539.94975340768179,116.4797973632812539.98132938627215,116.4756774902343839.99395569397331,116.4550781250000140.000267972646796,116.4385986328124940.000267972646796,116.416625976562539.998163944585805,116.3658142089843840.00868343656941,116.3520812988281240.00447583427404,116.3026428222656240.01078714046552,116.2779235839843639.999215966720165,116.2477111816406139.99500778093748,116.2326049804687439.990799335838034,116.2120056152343839.95606977009003,116.207885742187539.928694653732364))
','产流区单元','1');
insertintowl_model_polygon(geom,name)values('SRID=4326;POLYGON((118.7638298539022830.94145000894207,118.7636745447949830.941584547525736,118.7635079648540630.941783659824637,118.7633984482040430.941924731032316,118.7633091610754330.942036894992782,118.7632704075118730.94208876002824,118.7632040139741330.942103072784164,118.7631183330843230.942151844969032,118.7629741262892430.94233241273298,118.7628403347440630.942507490217793,118.7627406146548330.942508998759877,118.7627270982403630.942414705157432,118.7626031296342730.941400575247428,118.7624624613404230.940958834692708,118.7624198391823730.940824987759868,118.7623547702053230.94068130925791,118.7623222288262930.940647540114867,118.7629378869635330.940087796711964,118.7630715674341730.939971500356137,118.7632706385777530.93979831612114,118.763555853992930.939541452438277,118.763726512955630.93939848398361,118.7637777025644330.939355600092142,118.7644191067256530.9388159785355,118.7646306415407530.938667159236218,118.7649534107022230.938493604345012,118.7652367250614130.938409477348614,118.765419738178630.9383707434975,118.7658298530727730.938323591604444,118.7662205340716430.9382963001612,118.7664333027922830.938318107809664,118.766480181505730.938337017341382,118.7665247735276430.938350675989682,118.766658279658630.938456597505137,118.7667367336965830.938603248874927,118.7667723610076130.938782266531803,118.7668454971108130.939149764149192,118.7670163288576130.93988929949859,118.767037634739530.939981532336844,118.766418776875330.94010020307178,118.7661498168615730.940150404326346,118.765894099167130.940243370814187,118.7656924757934630.940342755588517,118.7655608931086130.940412552128976,118.7655203696626830.940466789099446,118.7655057391203930.940574355758315,118.7655121796831330.941150469586262,118.7655109857581730.941290908017095,118.7655098993600430.941418699044846,118.7654226075677630.94141695016964,118.7649912173150130.941408306476433,118.7639193700700830.94138581330907,118.7638298539022830.94145000894207))','产流区单元');
4.pg库清空数据和主键自增
TRUNCATETABLEwl_model_polygon;
TRUNCATEwl_model_polygonRESTARTIDENTITY;
5.相交分析sql
selectgid,name,ST_AsGeoJson(geom)asgeomfromwl_model_polygontwhereST_Intersects(t.geom,ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[118.78355107920095,30.938155072659868],[118.78134774048146,30.939763084116294],[118.7812957819458,30.93972376187253],[118.78286595934765,30.93838280705404],[118.7833158576293,30.93793078253492],[118.78355013577584,30.938153972966006],[118.78355107920095,30.938155072659868]]]}
'))
selectgid,name,ST_AsGeoJson(geom)asgeomfromwl_model_polygontwhereST_Intersects(t.geom,ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[118.78355107920095,30.938155072659868],[118.78134774048146,30.939763084116294],[118.7812957819458,30.93972376187253],[118.78286595934765,30.93838280705404],[118.7833158576293,30.93793078253492],[118.78355013577584,30.938153972966006],[118.78355107920095,30.938155072659868]]]}'))
四、自增序列
1、navicat创建自增字段
设置为serial4类型
保存后自动加序列
2、重置自增序列号为指定数值
第一步:selectpg_get_serial_sequence('ts_mapservice','f_remark');查看序列为public.ts_mapservice_f_remark_seq
第二步:更新序列值ALTERSEQUENCEpublic.ts_mapservice_f_remark_seqRESTARTWITH8;
或者直接初始化自增数值:TRUNCATETABLEwl_model_polygon;TRUNCATEwl_model_polygonRESTARTIDENTITY;
|