Question

是否有可能从两个单独的邮政数据库浏览JOIN?

我正在与一个服务器的系统联通数据库,有时我确实需要这种功能。

Answer 1

http://wiki.postgresql.org/wiki/FAQ#How_do_I_perform_queries_using_multiple_databases.3F”rel=“noreferer” http://wiki.postgresql.org/wiki/FAQ

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.

<>strong>EDIT:3年后(2014年版),这一编号已经修订,更有助益:

www.un.org/Depts/DGACM/index_spanish.htm 我如何利用多个数据库进行查询?

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.

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 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.

Answer 2

欲了解干线。

Say hello to Postgres_FDW :

准备使用<代码>指令_fdw进行远程访问:

Install the postgres_fdw 扩展使用<条码>。

Create a foreign server object, using CREATE SERVER, to represent each remote database you want to connect to. Specify connection information, except user, and password, as options of the server object.

Create a user mapping, using CREATE USER MAPPING, for each database user you want to allow to access each foreign server. Specify the remote user name and password to use as user and password options of the user mapping.

Create a foreign table, using CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA, for each remote table you want to access. The columns of the foreign table must match the referenced remote table. You can, however, use table and/or column names different from the remote table s, if you specify the correct remote names as options of the foreign table object.

Now you need only SELECT from a foreign table to access the data stored in its underlying remote table.

即便在大数据方面,它也确实有用。

Answer 3

是的,可以采用<代码>dblink,尽管有重大业绩考虑。

The following example will require the current SQL user to have permissions on both databases. If db2 is not located on the same cluster, then you will need to replace dbname=db2 with the full connection string defined in the dblink documentation.

SELECT * 
FROM   table1 tb1 
LEFT   JOIN (
   SELECT *
   FROM   dblink( dbname=db2 , SELECT id, code FROM table2 )
   AS     tb2(id int, code text);
) AS tb2 ON tb2.column = tb1.column;

如果<代码>表2非常大,你可能会有业绩问题,因为在加入之前,子座的负荷是整个<代码>>表2。

Answer 4

Just a few steps and You can reach the goal: follow this reference step by step

WE HAVE BEEN CONNECTED TO DB2 WITH TABLE TBL2 AND COLUMN COL2
ALSO THERE IS DB1 WITH TBL1 AND COLUMN COL1

 *** connecting to second db ie db2
    Now just **copy paste the 1-7 processes** (make sure u use correct username and password and ofcourse db name)

    1.**CREATE EXTENSION dblink;**

    2.**SELECT pg_namespace.nspname, pg_proc.proname 
    FROM pg_proc, pg_namespace 
    WHERE pg_proc.pronamespace=pg_namespace.oid 
       AND pg_proc.proname LIKE  %dblink% ;**

    3.**SELECT dblink_connect( host=localhost user=postgres password=postgres dbname=db1 );**

    4.**CREATE FOREIGN DATA WRAPPER postgres VALIDATOR postgresql_fdw_validator;**

    5.**CREATE SERVER postgres2 FOREIGN DATA WRAPPER postgres OPTIONS (hostaddr  127.0.0.1 , dbname  db1 );**

    6.**CREATE USER MAPPING FOR postgres SERVER postgres2 OPTIONS (user  postgres , password  postgres );**

    7.**SELECT dblink_connect( postgres2 );**

    ---Now, you can SELECT the data of Database_One from Database_Two and even join both db results:

    **SELECT * FROM public.dblink
    ( postgres2 , SELECT col1,um_name FROM public.tbl1  ) 
    AS DATA(um_userid INTEGER),tbl2 where DATA.col1=tbl2.col2;**


You can also Check this :[How to join two tables of different databases together in postgresql [[working finely in version 9.4]][1]

Answer 5

你们都不会。您可使用,从一个数据库链接到另一个数据库,但如果你重新研究JOIN,则会赢得一定帮助。

你们可以在单一数据库内使用不同的SCHEMA数据储存。

Answer 6

你们需要使用上文提到的dblink..., 就像这一作品一样:

select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
from information_schema.Columns ST
full outer join dblink( dbname=otherdatabase , select Table_Name,
Column_Name from information_schema.Columns ) DV(Table_Name text,
Column_Name text)
on ST.Table_Name = DV.Table_name
and ST.Column_Name = DV.Column_Name
where ST.Column_Name is null or DV.Column_Name is NULL

Answer 7

你们利用了干线延伸。

Reference take from this Article:

DbLink extension of PostgreSQL which is used to connect one database to another database.

Install DbLink 延期。

CREATE EXTENSION dblink;

Verification DbLink:

SELECT pg_namespace.nspname, pg_proc.proname 
FROM pg_proc, pg_namespace 
WHERE pg_proc.pronamespace=pg_namespace.oid 
   AND pg_proc.proname LIKE  %dblink% ;

我已经准备对此进行充分的示范。请访问我的员额,以便逐步学习执行Pogresql交叉数据库查询。

Answer 8

做不到吗? Of course we can, without special extensions. 就我们而言,我们必须比较不同数据库服务器的两张表格,例如行政协调会和PROD,因此比多数答复更困难。尤其因为行政协调会和PROD被故意放在不同的服务器上制造障碍,因此,你不会轻易获得足够的权利,以履行《欧洲原子能共同体条约》所规定的义务。

明显的解决办法是出口表格,并在同一个数据库(如DEV)或你自己的地方db)中以适当名称进口,如表1_acc和表1_prod,或如acc和prod。然后,你可以发现没有特殊问题的人。

友情链接