Saturday, November 24, 2012

PostgreSQL - Query data from another database/server

Assume that, you have two databases:
1. db1 with two schemas: public and sch1
2. db2 with two schemas: public and sch2

In PostgreSQL, you can access a table from a different schema by using SELECT * FROM schema_name.table_name such as SELECT * FROM db1_sch.employee;
However, you cannot access a table on the different database with explicit way like SELECT * FROM db2.sch2.employee;

The question is how can we access like SQL Server or some other DBMS.
Fortunately, PostgreSQL support dblink to do that.

First, install extension dblink on the database you want to be able to access to other
CREATE EXTENSION dblink;

If db2 is in the same server, you can query table in db2 like that:
SELECT *
FROM dblink('dbname=db2', 'SELECT employee_id, employee_name FROM sch2.employee')
           AS employee(employee_id integer, employee_name text);

If db2 is in the different server, you can query:
SELECT *
FROM dblink('hostaddr=172.x.x.x dbname=db2 user=admin password=pwd port=5432', 'SELECT employee_id, employee_name FROM sch2.employee')
           AS employee(employee_id integer, employee_name text);

1 comment:

  1. It's truly very difficult in this active life to listen news on TV, thus I simply use web for that reason, and get the hottest news.

    Here is my web blog - small business reputation management

    ReplyDelete

Oracle Query