May 16 2018

How to Using Dblink With DML Operations in PostgreSQL 9

Category: Administration,Errors and Solutions,SQLFatih Acar @ 11:45

Dblinks provide to connect another databases. Also you can do DML operations with using dblink. I will use dblink to connect two PostgreSQL databases on this post. Also I will tell how to use select, update and delete with dblink.

Firstly, you have to create dblink entension with related user which is using dblink.

Create Dblink Extension


postgres@testdb ~]# psql

psql> create extension dblink

Dblink Connection Test


psql> select dblink_connect('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword');

If result is OK, your connection test is successful.

Select Operation With Dblink


psql> select * from dblink('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','select id,name from remotetable') 
as t(id numeric(10,0),name character varying(100));

Update Operation With Dblink


psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = 1');

You can use dynamic variable to where condition.

psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = ' || ID || '');

If you get an error like "ERROR: query has no destination for result data, if you want to discard the results of a select, use perform instead.", you can use perform statement instead of select.

psql> perform dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = 1');

Delete Operation With Dblink


psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = 1');

You can use dynamic variable to where condition.

psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = ' || ID || '');

If you get an error like "ERROR: query has no destination for result data, if you want to discard the results of a select, use perform instead.", you can use perform statement instead of select.

psql> perform dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = 1');

2,848 total views, 4 views today

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.