连接来自两个独立数据库的结果 [英] 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 的只读版本一个贡献模块.

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屋！