连接来自两个单独数据库的结果 [英] Joining Results from Two Separate Databases
问题描述
是否可以从两个独立的postgres数据库中JOIN
行?
Is it possible to JOIN
rows from two separate postgres databases?
我正在使用一台服务器中具有几个数据库的系统,有时我确实需要这样的功能.
I am working with system with couple databases in one server and sometimes I really need such a feature.
推荐答案
根据 http: //wiki.postgresql.org/wiki/FAQ
除了当前数据库外,没有其他方法可以查询数据库. 因为PostgreSQL加载特定于数据库的系统目录,所以它是 不确定跨数据库查询的行为. contrib/dblink允许使用函数调用进行跨数据库查询.的 当然,客户也可以同时连接到不同的 数据库并在客户端合并结果.
There is no way to query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave. contrib/dblink allows cross-database queries using function calls. Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.
编辑:3年后(2014年3月),此常见问题解答条目已被修改,对您的帮助更大:
EDIT: 3 years later (march 2014), this FAQ entry has been revised and is more helpful:
如何使用多个数据库执行查询?
除了当前数据库外,没有其他方法可以直接查询数据库 一.因为PostgreSQL加载特定于数据库的系统目录,所以它是 不确定跨数据库查询的行为方式.
There is no way to directly query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave.
PostgreSQL中的SQL/MED支持允许外部数据包装器"来 创建后,将远程数据库中的表链接到本地数据库. 远程数据库可能是同一PostgreSQL上的另一个数据库 实例或遍布全球的数据库,都没关系. postgres_fdw内置于PostgreSQL 9.3,包括读/写 支持;可以将9.2的只读版本编译并安装为 一个contrib模块.
The SQL/MED support in PostgreSQL allows a "foreign data wrapper" to be created, linking tables in a remote database to the local database. The remote database might be another database on the same PostgreSQL instance, or a database half way around the world, it doesn't matter. postgres_fdw is built-in to PostgreSQL 9.3 and includes read/write support; a read-only version for 9.2 can be compiled and installed as a contrib module.
contrib/dblink允许使用函数调用和跨数据库查询 适用于许多较旧的PostgreSQL版本.不像postgres_fdw 它无法将条件推送"到远程服务器,因此它经常 可以获取比您所需更多的数据.
contrib/dblink allows cross-database queries using function calls and is available for much older PostgreSQL versions. Unlike postgres_fdw it can't "push down" conditions to the remote server, so it'll often land up fetching a lot more data than you need.
当然,客户端也可以同时连接到 不同的数据库并将结果合并到客户端.
Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.
这篇关于连接来自两个单独数据库的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!