PostgreSQL 跨库查询方法

问题描述：

PostgreSQL 跨库查询方法

select rc.tm,fq,fq_old,sccd,unitcd from resource_calc_scene_section_result as rcinner join \"mxpt_yushuiqingsrc_databases\".\"public\".st_river_r_hi as st on st.stcd= rc.bscd and st.tm = rc.tm where 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 rcINNER 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.tmWHERE 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_serverFOREIGN DATA WRAPPER postgres_fdwOPTIONS (host \'localhost\', dbname \'mxpt_yushuiqingsrc_databases\');-- 创建用户映射CREATE USER MAPPING FOR current_userSERVER foreign_serverOPTIONS (user \'username\', password \'password\');-- 创建外部表CREATE FOREIGN TABLE foreign_st_river_r_hi ( stcd text, tm timestamp -- 其他字段...)SERVER foreign_serverOPTIONS (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 rcINNER JOIN foreign_st_river_r_hi AS st ON st.stcd = rc.bscd AND st.tm = rc.tmWHERE rc.scprod_id IN (\'13524\')ORDER BY rc.bscd, rc.tm;

3. 使用临时表或物化视图

如果不能使用上述方法，可以先将外部数据库的数据导入到当前数据库：

-- 创建临时表CREATE TEMP TABLE temp_st_river_r_hi ASSELECT * 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 rcINNER JOIN temp_st_river_r_hi AS st ON st.stcd = rc.bscd AND st.tm = rc.tmWHERE 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.qFROM resource_calc_scene_section_result AS rcINNER 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.tmWHERE rc.scprod_id IN (\'13524\')ORDER BY rc.bscd, rc.tm;