Cross – database query in PostgreSQL :

We can join results from two different databases in PostgreSQL by using dblink/postgres_fdw( foreign database wrapper).

Postgres_fdw : this feature is available in Postgres 9.3 onwards.By using we can connect to tables in any Postgres – Local or remote.

DbLink :DbLink returns a generic data row_type. In order to use this , we need to specify the output structure in advance .It is located in share/contribs/dblink.sql of your PostgreSQL install.Using Dblink , we an query database on the same server as well as on cluster.

Below are few examples :

1. SELECT employee.empno,employee.ename  FROM dblink(‘dbname=foodmart’,’SELECT empno, ename FROM emp) AS employee(empno integer , ename varchar(20));

If the database is on different port : 

  1. SELECT employee.empno,employee.ename  FROM dblink(‘dbname=foodmart’, port=5433′,     ‘SELECT empno, ename FROM emp) AS employee(empno integer , ename varchar(20));

 If we want to query database from remote server :

  1. SELECT employee.empno,employee.ename FROM dblink(‘dbname=foodmart’, port=5433′,host=host_name,user=user_name,password=pwd ,’SELECT empno, ename FROM emp)  AS employee(empno integer , ename varchar(20));

 

Thanks,

Rupam Bhardwaj

 

 

 

 

 

Leave a Reply