Joining Results from Two Separate Databases

Question

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.

Carthage

Answer 1

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

EDIT: 3 years later (march 2014), this FAQ entry has been revised and is more helpful:

How do I perform queries using multiple databases?

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.

Manisa

Answer 2

Forget about dblink!

Say hello to Postgres_FDW:

To prepare for remote access using postgres_fdw:

1. Install the postgres_fdw extension using CREATE EXTENSION.

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

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

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

It's really useful even on large data.

Babylonia

This is great until I reach #4 where you say that we need to create the foreign table. Basically we have 3 separate databases that I need to query as if they were 1. Although if any change happens to any of the 3 databases the queries should always take the latest schema and data changes. Is this possible with FDW? – Whaleboat

You can always update the definitions to reflect the target tables. It's really fast and worth any effort. – Babylonia

Answer 3

Yes, it is possible to do this using dblink albeit with significant performance considerations.

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;

If table2 is very large, you could have performance issues because the sub-query loads up the entire table2 before performing the join.

Blake

Does not work for me. I am getting Query 1 ERROR: ERROR: syntax error at or near ";" – Electroform

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]

Apoplectic

Answer 5

No you can't. You could use dblink to connect from one database to another database, but that won't help if you're looking for JOIN's.

You can't use different SCHEMA's within a single database to store all you data?

Exon

I cannot, We have some databases full of data and no one want to change anything:/ If I'll use this dblink, can I do something like that: INSERT INTO DB1.table SELECT * FROM DB2.table; – Carthage

You can certainly do that if you execute the query in db1, sth like INSERT INTO db1_table(cols...) SELECT cols... FROM dblink('db2', 'SELECT cols... FROM db2_table') – Intercurrent

I have used this dblink() to connect this it works for me always – Porterfield

@Intercurrent Is cols... an actual syntax shortcut, or are you just using that to indicate that he needs to enumerate the columns in all three locations in the statement? – Lamond

@Lamond right, cols... isn't actual syntax, just an abbreviation in the comment – Intercurrent

Can you update for nowadays? I am supposing that the best option is to use FDW, as the other answer says... But only supposing... – Decimal

Answer 6

You need to use dblink...as araqnid mentioned above, something like this works fine:

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

Heirdom

Answer 7

You have use dblink extension of postgresql.

Reference take from this Article:

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

Install DbLink extension.

CREATE EXTENSION dblink;

Verify 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%';

I have already prepared full demonstration on this. Please visit my post to learn step by step for executing cross database query in Postgresql.

Humorous

Good catch. Not everyone knows that you need the extension to be active. – Tensor

Answer 8

Cannot be done? Of course we can, without special extensions. In our case, we had to compare two tables from different database servers, e.g. ACC and PROD, hence an even harder case than from most answers. Especially because ACC and PROD are deliberately on different servers to create a barrier, so you will not easily gain enough rights to perform a GRANT USAGE ON FOREIGN SERVER.

The obvious solution is to export both tables, and import both in the same database, e.g. DEV, or your own local db, under appropriate names, e.g. table1_acc and table1_prod, or schemas like acc and prod. Then, you may JOIN those with no special problems.

Nosegay