Join tables from different databases (PostgreSQL)

Question

Is it possible to join tables from different databases located in the same server, using PostgreSQL? If so, how?

Solution 1

Let's suppose you are in database db1 in postgres. Then,

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

would join tb1 and tb2 (your other table from different database) on said column. Here in the example I have used dblink to do this. tb1 and tb2 represent your tables. Replace table1 and table2 with your table names and db2 with your other database name.

Solution 2

You can use dblink or foreign_table through postgresql_fdw.

1. dblink

dblink is a module that supports connections to other PostgreSQL databases from within a database session.

You can read document here.

2. Foreign Table

The postgres_fdw module provides the foreign-data wrapper postgres_fdw, which can be used to access data stored in external PostgreSQL servers. The functionality provided by this module overlaps substantially with the functionality of the older dblink module. But postgres_fdw provides more transparent and standards-compliant syntax for accessing remote tables, and can give better performance in many cases.

You can read document here.