sqlpostgresqljoincross-database

Join tables from different databases (PostgreSQL)

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

Solution

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