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 :
- 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 :
- 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
Best Open Source Business Intelligence Software Helical Insight is Here
