PostgreSQL 跨库查询方法
问题描述:
PostgreSQL 跨库查询方法
select rc.tm,fq,fq_old,sccd,unitcd from resource_calc_scene_section_result as rc
inner join "mxpt_yushuiqingsrc_databases"."public".st_river_r_hi as st on st.stcd= rc.bscd and st.tm = rc.tmwhere scprod_id in ('13524' ) order by bscd,rc.tm;
postgresql数据库,如何实现跨库联查呢?
解决方案:
在 PostgreSQL 中实现跨库联查有几种方法,因为 PostgreSQL 本身不支持直接的跨库查询(不像 MySQL 的 database.table 语法)。以下是几种解决方案:
1. 使用 dblink 扩展
这是最常用的方法,需要先安装 dblink 扩展:
-- 首先安装扩展
CREATE EXTENSION IF NOT EXISTS dblink;-- 然后使用 dblink 进行跨库查询
SELECT rc.tm, rc.fq, rc.fq_old, rc.sccd, rc.unitcd
FROM resource_calc_scene_section_result AS rc
INNER JOIN dblink('dbname=mxpt_yushuiqingsrc_databases', 'SELECT stcd, tm FROM public.st_river_r_hi') AS st(stcd text, tm timestamp) ON st.stcd = rc.bscd AND st.tm = rc.tm
WHERE rc.scprod_id IN ('13524')
ORDER BY rc.bscd, rc.tm;
2. 使用 FDW (Foreign Data Wrapper)
这是更现代的解决方案,可以创建外部表:
-- 安装 postgres_fdw 扩展
CREATE EXTENSION IF NOT EXISTS postgres_fdw;-- 创建服务器连接
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'mxpt_yushuiqingsrc_databases');-- 创建用户映射
CREATE USER MAPPING FOR current_user
SERVER foreign_server
OPTIONS (user 'username', password 'password');-- 创建外部表
CREATE FOREIGN TABLE foreign_st_river_r_hi (stcd text,tm timestamp-- 其他字段...
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'st_river_r_hi');-- 然后就可以像普通表一样查询
SELECT rc.tm, rc.fq, rc.fq_old, rc.sccd, rc.unitcd
FROM resource_calc_scene_section_result AS rc
INNER JOIN foreign_st_river_r_hi AS st ON st.stcd = rc.bscd AND st.tm = rc.tm
WHERE rc.scprod_id IN ('13524')
ORDER BY rc.bscd, rc.tm;
3. 使用临时表或物化视图
如果不能使用上述方法,可以先将外部数据库的数据导入到当前数据库:
-- 创建临时表
CREATE TEMP TABLE temp_st_river_r_hi AS
SELECT * FROM dblink('dbname=mxpt_yushuiqensrc_databases', 'SELECT * FROM public.st_river_r_hi') AS t(stcd text, tm timestamp);-- 然后查询
SELECT rc.tm, rc.fq, rc.fq_old, rc.sccd, rc.unitcd
FROM resource_calc_scene_section_result AS rc
INNER JOIN temp_st_river_r_hi AS st ON st.stcd = rc.bscd AND st.tm = rc.tm
WHERE rc.scprod_id IN ('13524')
ORDER BY rc.bscd, rc.tm;
注意事项
确保有足够的权限访问两个数据库
跨库查询可能影响性能,特别是大数据量时
考虑网络延迟(如果数据库在不同的服务器上)
FDW 方法需要 PostgreSQL 9.3 或更高版本
对于您的具体查询,我建议使用 dblink 或 FDW 方法,因为它们更灵活且不需要数据复制。
改写后的sql语句:
SELECT rc.bscd, rc.tm, rc.fq, rc.fq_old, rc.sccd, rc.unitcd, st.tm, st.q
FROM resource_calc_scene_section_result AS rc
INNER JOIN dblink('host=180.451.242.185 port=30002 dbname=postgres user=yw5pt password=pgread@HL202555', 'SELECT stcd, tm, q FROM md.st_river_r_hi where tm >= ''2025-07-20 08:00:00'' and tm <= ''2025-07-20 08:00:00''') AS st(stcd text, tm timestamp, q numeric) ON st.stcd = rc.bscd AND st.tm = rc.tm
WHERE rc.scprod_id IN ('13524')
ORDER BY rc.bscd, rc.tm;