postgresql使用dblink跨庫(kù)增刪改查的步驟
更新時(shí)間：2023年04月01日 11:25:01 作者：EricFRQ
這篇文章主要介紹了postgresql使用dblink跨庫(kù)增刪改查，本文給大家介紹的非常詳細(xì)對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
postgresql使用dblink跨庫(kù)增刪改查
一、使用步驟
1、創(chuàng)建dblink擴(kuò)展，連接與被連接的兩個(gè)數(shù)據(jù)庫(kù)都要執(zhí)行下面sql
create extension if not exists dblink;
2、跨庫(kù)查詢或增刪改
#查詢
SELECT
*
FROM
dblink ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'select user_id,account from piedss_biz.sys_user' ) AS T ( ID TEXT, NAME TEXT);
#新增
SELECT dblink_exec ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'INSERT INTO piedss_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'',''管理員'') ' );
#將庫(kù)A的數(shù)據(jù)查詢出來(lái)后直接插入存庫(kù)B
INSERT INTO dms_usercenter_userinfo ( ID, true_name, username, PASSWORD, phone ) SELECT
*
FROM
dblink ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'SELECT sys_user.user_id,sys_user.real_name,sys_user.account,sys_user.password,sys_user.tel FROM piedss_biz.sys_user' ) AS T ( ID TEXT, true_name TEXT, username TEXT, PASSWORD TEXT, phone TEXT );
#修改
SELECT dblink_exec ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'UPDATE piedss_biz.sys_user SET account=''ericfrq'',password=''1qazWSX'',sex=''F'',super_admin_flag=''Y'',status_flag=''1'',del_flag=''N'',create_user=''dms_datahub'',real_name=''管理員''WHERE user_id=''158800689501958963111''' );
#刪除
SELECT dblink_exec ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'DELETE FROM piedss_biz.sys_user WHERE user_id=''4028db8283d486350183d533f7570000'' AND create_user=''dms_datahub''' );3、如果不想每一次都寫完整的dblink連接信息，可以先起別名
#起別名
select dblink_connect('bieming', 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26');
#進(jìn)行操作
SELECT dblink_exec ( 'bieming', 'INSERT INTO piedss_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'',''管理員'') ' );
#關(guān)閉連接
SELECT dblink_disconnect('bieming');4、補(bǔ)充：mybatis直接執(zhí)行上面的sql寫法
參考下面補(bǔ)充介紹：pgsql個(gè)人筆記，mybatis+postgresql寫原生sql，不用xml
補(bǔ)充：pgsql個(gè)人筆記
一、mybatis+pgsql的xml
下面統(tǒng)計(jì)的sql中用到的聚合函數(shù)具體解析說明： 第一部分
- array_to_string( ARRAY_AGG ( stp.source_server ), ',' )： 將stp的source_server的數(shù)據(jù)轉(zhuǎn)化為數(shù)組，再以逗號(hào)分隔拼接起來(lái)轉(zhuǎn)成字符串。
- array_to_string( ARRAY_AGG ( stp.target_server ), ',' )將stp的target_server的數(shù)據(jù)轉(zhuǎn)化為數(shù)組，再將數(shù)組轉(zhuǎn)換為字符串，用“，”分隔。（有點(diǎn)類似于Mysql的group_concat()函數(shù)）
- concat_ws ( ',', 'a', 'b' ) :將ab以逗號(hào)連接。在下面的案例中：concat_ws ( ',', array_to_string( ARRAY_AGG ( stp.source_server ), ',' ), array_to_string( ARRAY_AGG ( stp.target_server ), ',' ) )是將第一步的兩個(gè)結(jié)果，合并成一個(gè)字符串
- regexp_split_to_table((a,b),',' ) :將a,b以逗號(hào)分隔開并將a、b分別作為表查詢的結(jié)果。在下面的案例中，regexp_split_to_table( ( concat_ws ( ',', array_to_string( ARRAY_AGG ( stp.source_server ), ',' ), array_to_string( ARRAY_AGG ( stp.target_server ), ',' ) ) ), ',' ) 將第二步的結(jié)果，以逗號(hào)“,”分隔,并把每一項(xiàng)作為結(jié)果
- DISTINCT將第三步的結(jié)果去重
- COUNT ( * )統(tǒng)計(jì)第四步去重后的數(shù)量
第二部分
- SUM ( stad.data_volume )，計(jì)算data_volume的和
- ROUND( '100' :: NUMERIC / 10, 3 )將100除以10后保留小數(shù)點(diǎn)后三位。其中:: NUMERIC將字符串’100’轉(zhuǎn)為數(shù)字(numeric類型最多能存儲(chǔ)有1000個(gè)數(shù)字位的數(shù)字并且能進(jìn)行準(zhǔn)確的數(shù)值計(jì)算。它主要用于需要準(zhǔn)確地表示數(shù)字的場(chǎng)合，如貨幣金額。不過，對(duì)numeric 類型進(jìn)行算術(shù)運(yùn)算比整數(shù)類型和浮點(diǎn)類型要慢很多。)。案例中ROUND( SUM ( stad.data_volume ) :: NUMERIC / ( 1024 * 1024 * 1024 ), 3 )將第一步的結(jié)果轉(zhuǎn)為字符串并除以1024的三次方（將字節(jié)B轉(zhuǎn)–>kb–>mb–>GB）
- CAST(oti.institution_id AS VARCHAR) 將int型institution_id轉(zhuǎn)為varchar型
- 將時(shí)間字段格式化為指定格式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") String sqlStr);
@Select({"${sqlStr}"})
List<ModelPolygon> exeNativeSql(@Param("sqlStr") String sqlStr);
//"select gid as gid,name as name,ST_AsGeoJson(geom) as geom,code as code from wl_model_polygon"整個(gè)dao層的寫法：
package com.xxx.mapper;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xxx.entity.UserInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import java.util.List;
@Mapper
public interface UserInfoMapper extends BaseMapper<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") String sqlStr);
@Select({"${sqlStr}"})
List<JSONObject> exeNativeExecSql(@Param("sqlStr") String sqlStr);
} <!--數(shù)據(jù)量統(tǒng)計(jì) -->
<select id="getDataByParams" resultType="com.htht.datatrans.app.vo.CountProtocolVO">
SELECT
*
FROM
(
SELECT COUNT
( * ) AS useNode
FROM
(
SELECT DISTINCT
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_protocol AS stp
WHERE
stp.deleted = 0
AND stp.protocol_type = 'data communication'
) res
) node1,
(
SELECT COUNT
( * ) AS runningNode
FROM
(
SELECT DISTINCT
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_protocol AS stp
WHERE
stp.deleted = 0
AND stp.protocol_type = 'data communication'
AND stp.run_state = 'running'
) res
) node2,
( SELECT COUNT ( * ) AS protocolTotal FROM sync_t_protocol AS stp WHERE stp.deleted = 0 AND stp.protocol_type = 'data communication' ) protocol1,
(
SELECT COUNT
( * ) AS runningProtocol
FROM
sync_t_protocol AS stp
WHERE
stp.deleted = 0
AND stp.protocol_type = 'data communication'
AND stp.run_state = 'running'
) protocol2,
(
SELECT COUNT
( * ) AS exceptionalProtocol
FROM
sync_t_protocol AS stp
WHERE
stp.deleted = 0
AND stp.protocol_type = 'data communication'
AND stp.run_state = 'exception'
) protocol3,
(
SELECT
ROUND( SUM ( stad.data_volume ) :: NUMERIC / ( 1024 * 1024 * 1024 ), 3 ) AS runningData
FROM
sync_t_action_detail AS stad
INNER JOIN sync_t_protocol AS stp ON stad.protocol_id = stp.protocol_id
AND stp.deleted = 0
AND protocol_type = 'data communication'
WHERE
stad.execute_state = ANY ( STRING_TO_ARRAY( 'running', ',' ) )
) data1,
(
SELECT
ROUND( SUM ( stad.data_volume ) :: NUMERIC / ( 1024 * 1024 * 1024 ), 3 ) AS historyData
FROM
sync_t_action_detail AS stad
INNER JOIN sync_t_protocol AS stp ON stad.protocol_id = stp.protocol_id
AND stp.deleted = 0
AND protocol_type = 'data communication'
WHERE
stad.execute_state = ANY ( STRING_TO_ARRAY( 'succeed,failed', ',' ) )
) data2
</select>
<select
id="getPagesByParams"
resultType="com.htht.datatrans.app.vo.CloudVO">
select * from ops_t_cloud where delete=0
<if test="cloudProvider != null and cloudProvider != ''">
and cloud_provider like '%'||#{cloudProvider,jdbcType=VARCHAR}||'%'
</if>
order by cloud_id
</select>
<select
id="getByCloudCodes"
resultType="com.htht.datatrans.app.entity.Cloud">
select * from ops_t_cloud where delete=0
<if test="cloudCodes != null and cloudCodes != ''">
and cloud_code = ANY(STRING_TO_ARRAY(#{cloudCodes,jdbcType=VARCHAR}, ','))
</if>
order by cloud_id
</select>
<select
id="getPagesByParams"
resultType="org.springblade.modules.datatrans.vo.ServerPageVO">
select ots.*,otc.cloud_name as cloudName,otc.domain_name as domainName
from ops_t_server ots
inner join ops_t_cloud otc on ots.cloud_id = otc.cloud_id
<if test="institutionId != null and institutionId != ''">
inner join ops_t_institution oti ON CAST(oti.institution_id AS VARCHAR) = ots.institution_id
</if>
where ots.deleted=0
<if test="cloudProvider != null and cloudProvider != ''">
and otc.cloud_provider like concat(concat('%',#{cloudProvider,jdbcType=VARCHAR}),'%')
</if>
order by ots.server_id
</select>
二、字符串替換
將address字段里的 “區(qū)” 替換為 “嘔” 顯示，如下
select *,replace(address,'區(qū)','嘔') AS rep from test_tb
將name字段里的 “我” 替換為 “你” 保存，如下
UPDATE blade_visual SET "name" = ( REPLACE ( NAME, '你', '你們?nèi)? ) )
三、postgre做空間數(shù)據(jù)分析
比如面相交
1、使用步驟 新建空間索引create extension postgis;創(chuàng)建geometry類型字段
3.插入geometry數(shù)據(jù)
insert into wl_model_polygon(geom,name,code) values ('SRID=4326;POLYGON ((116.2078857421875 39.928694653732364, 116.20925903320312 39.91078961774283, 116.20651245117188 39.89393354266699, 116.23397827148436 39.86547951378614, 116.24496459960938 39.82752244475985, 116.29852294921876 39.78954439311165, 116.3397216796875 39.78532331459258, 116.3836669921875 39.78848914776114, 116.41799926757811 39.79904087286648, 116.444091796875 39.80748108746673, 116.45919799804688 39.818029898770206, 116.48117065429686 39.83490462943255, 116.50314331054688 39.86231722624386, 116.50588989257812 39.88023492849342, 116.5045166015625 39.90973623453719, 116.4935302734375 39.925535281697286, 116.5045166015625 39.94975340768179, 116.47979736328125 39.98132938627215, 116.47567749023438 39.99395569397331, 116.45507812500001 40.000267972646796, 116.43859863281249 40.000267972646796, 116.4166259765625 39.998163944585805, 116.36581420898438 40.00868343656941, 116.35208129882812 40.00447583427404, 116.30264282226562 40.01078714046552, 116.27792358398436 39.999215966720165, 116.24771118164061 39.99500778093748, 116.23260498046874 39.990799335838034, 116.21200561523438 39.95606977009003, 116.2078857421875 39.928694653732364))
','產(chǎn)流區(qū)單元','1');
insert into wl_model_polygon(geom,name) values ('SRID=4326;POLYGON ((118.76382985390228 30.94145000894207, 118.76367454479498 30.941584547525736, 118.76350796485406 30.941783659824637, 118.76339844820404 30.941924731032316, 118.76330916107543 30.942036894992782, 118.76327040751187 30.94208876002824, 118.76320401397413 30.942103072784164, 118.76311833308432 30.942151844969032, 118.76297412628924 30.94233241273298, 118.76284033474406 30.942507490217793, 118.76274061465483 30.942508998759877, 118.76272709824036 30.942414705157432, 118.76260312963427 30.941400575247428, 118.76246246134042 30.940958834692708, 118.76241983918237 30.940824987759868, 118.76235477020532 30.94068130925791, 118.76232222882629 30.940647540114867, 118.76293788696353 30.940087796711964, 118.76307156743417 30.939971500356137, 118.76327063857775 30.93979831612114, 118.7635558539929 30.939541452438277, 118.7637265129556 30.93939848398361, 118.76377770256443 30.939355600092142, 118.76441910672565 30.9388159785355, 118.76463064154075 30.938667159236218, 118.76495341070222 30.938493604345012, 118.76523672506141 30.938409477348614, 118.7654197381786 30.9383707434975, 118.76582985307277 30.938323591604444, 118.76622053407164 30.9382963001612, 118.76643330279228 30.938318107809664, 118.7664801815057 30.938337017341382, 118.76652477352764 30.938350675989682, 118.7666582796586 30.938456597505137, 118.76673673369658 30.938603248874927, 118.76677236100761 30.938782266531803, 118.76684549711081 30.939149764149192, 118.76701632885761 30.93988929949859, 118.7670376347395 30.939981532336844, 118.7664187768753 30.94010020307178, 118.76614981686157 30.940150404326346, 118.7658940991671 30.940243370814187, 118.76569247579346 30.940342755588517, 118.76556089310861 30.940412552128976, 118.76552036966268 30.940466789099446, 118.76550573912039 30.940574355758315, 118.76551217968313 30.941150469586262, 118.76551098575817 30.941290908017095, 118.76550989936004 30.941418699044846, 118.76542260756776 30.94141695016964, 118.76499121731501 30.941408306476433, 118.76391937007008 30.94138581330907, 118.76382985390228 30.94145000894207))','產(chǎn)流區(qū)單元');4.pg庫(kù)清空數(shù)據(jù)和主鍵自增
TRUNCATE TABLE wl_model_polygon; TRUNCATE wl_model_polygon RESTART IDENTITY;
5.相交分析sql
select gid,name,ST_AsGeoJson(geom) as geom from wl_model_polygon t where ST_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]]]}
'))
select gid,name,ST_AsGeoJson(geom) as geom from wl_model_polygon t where ST_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創(chuàng)建自增字段
設(shè)置為serial4類型
保存后自動(dòng)加序列
2、重置自增序列號(hào)為指定數(shù)值
第一步：select pg_get_serial_sequence('ts_mapservice', 'f_remark');查看序列為public.ts_mapservice_f_remark_seq
第二步：更新序列值ALTER SEQUENCE public.ts_mapservice_f_remark_seq RESTART WITH 8;
或者直接初始化自增數(shù)值：TRUNCATE TABLE wl_model_polygon; TRUNCATE wl_model_polygon RESTART IDENTITY;
到此這篇關(guān)于postgresql使用dblink跨庫(kù)增刪改查的文章就介紹到這了,更多相關(guān)postgresql跨庫(kù)增刪改查內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了PostgreSQL之INDEX 索引詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-02-02
PostgreSQL 實(shí)現(xiàn)子查詢返回多行的案例
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)子查詢返回多行的案例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
PostgreSQL 如何獲取當(dāng)前日期時(shí)間及注意事項(xiàng)
這篇文章主要介紹了PostgreSQL 如何獲取當(dāng)前日期時(shí)間及注意事項(xiàng)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-10-10
Ruoyi從mysql切換到postgresql的幾個(gè)踩坑實(shí)戰(zhàn)
最近由于工作的原因,需要將Ruoyi從mysql切換到postgresql,所以這篇文章主要給大家介紹了關(guān)于Ruoyi從mysql切換到postgresql的幾個(gè)踩坑實(shí)戰(zhàn),需要的朋友可以參考下2023-02-02
CentOS 7下安裝PostgreSQL 9.6的教程分享
PostgreSQL在我心目中的地位要遠(yuǎn)遠(yuǎn)高于MySQL，雖然流行對(duì)比MySQL低很對(duì)，但是功能性一致走在MySQL的前面。下面這篇文章主要介紹了CentOS 7下安裝PostgreSQL數(shù)據(jù)庫(kù)的方法,需要的朋友可以參考借鑒，一起來(lái)看看吧。2017-02-02
這篇文章主要介紹了PostgreSQL備份工具 pgBackRest使用詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-02-02
這篇文章主要介紹了GP如何查詢并刪除重復(fù)數(shù)據(jù)問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-11-11
這篇文章主要介紹了Postgresql 默認(rèn)用戶名與密碼的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
這篇文章主要介紹了修改postgresql存儲(chǔ)目錄的操作方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
這篇文章主要介紹了PostgreSQL刪除更新優(yōu)化操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2020-12-12