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


SELECT *

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

AS emp( id int,ename varchar);

here

  • 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


SELECT *

FROM dblink('dbname=test1 host=192.10.1.5','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

SELECT t1.*

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 :

select

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


for delete :

select

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


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

3 comments:

Anonymous said...

any dblink equivalent in windows

Unknown said...

awesome blogs..

Paperworldmusic said...

Thhis was great to read