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');

390 total views, 4 views today


Jun 14 2016

ERROR: database “database_name” is being accessed by other users in PostgreSQL

Category: Administration,Errors and SolutionsFatih Acar @ 08:57

Error

When you execute drop database command, database get an error like ERROR: database “database_name” is being accessed by other users.>

droppostgresqldatabase1

droppostgresqldatabase2

Solution
Continue reading “ERROR: database “database_name” is being accessed by other users in PostgreSQL”

1,756 total views, no views today

Tags: PostgreSQL, PostgreSQL Administration


Nov 14 2014

PostgreSQL Error : invalid byte sequence for encoding utf8

Category: Errors and SolutionsFatih Acar @ 11:52

Error

PostgreSQL Error : invalid byte sequence for encoding UTF8

When I try insert a row with Turkish character, I get an error like above.

Solution
Continue reading “PostgreSQL Error : invalid byte sequence for encoding utf8”

3,488 total views, 10 views today