Thursday, August 12, 2010

Querying cross database

for querying cross database should install dblilnk which is available under postgresql-contrib-xxx package.

installation on unix/debain/boss

$ apt-get install postgresql-contrib-8.3

$ su postgres

$ psql dbName < /usr/share/postgresql/8.3/contrib/dblink.sql

now you could be able to querying from different database or server

1) querying from other database on same server


FROM dblink('dbname=test1','select id,name from emp')

AS emp( id int,ename varchar);


  • host --- specify the remote server ip
  • dbname -- specify the database name
  • port -- specify port no other the default (5432)
  • username -- specify the username (if you did not specify this, dblink will connect
  • password -- specify the password ( with current username & password)

since we are using querying on same server we omit others'

also we need to specify output structure of the table like AS emp( id int,ename varchar); with out this we may get error like

" ERROR: a column definition list is required for functions returning "record" "

2) Querying other database from remote server


FROM dblink('dbname=test1 host=','select id from students')

AS stu(id int);

here we ip address of remote host using 'host' parameter.

3) Bulk insert into local table from remote server


INTO test2

FROM dblink('dbname=saas-spms', 'SELECT * FROM test1)

AS t1(emp_no integer, degree varchar);

here table test2 should be new table, if already exist our database it shows error like : relation "test2" already found.

4) querying into remote server
If we want local table insert/update into remote postgres server
we can try like,

for insert :


dblink_exec('dbname=test1 host=','insert into emp(id,ename) values(8,''employeeName'')')

for delete :


dblink_exec('dbname=test1 host=','delete from emp where id=8')

dblink --> connect with remote server to execute the query which is return row (i.e Select )
dblink_exec --> connect to remote server and execute query which is not return row.

Post a Comment