Search code examples
sqlpostgresqldblinkpostgres-fdw

How can you do an update to a table in another database using postgres dblink?

The query that has to be executed is as simple as below - 

Update employee set is_done=true;

The table that I want to update is only present in another database.

I have been using these kinds of dblink queries. 

INSERT Into mytable select * from 
dblink('host=10.1.1.1
 user=user
 password=password
 dbname=oat', 'SELECT * from employee') tt(
     user_id integer,
     is_done boolean

 ) on conflict(user_id) do nothing;

How can I update a field of employee table, which is on another database?

I also want to know if we can achieve doing a delete as well in a similar manner - delete an entire row for a given id

Also, what if I had to do a join with the current database table in the update query?

Solution

  • This worked for me. 

     select * from dblink('host=10.1.1.1
     user=user
     password=password
     dbname=oat','Update employee set is_done =true' ) tt(
     updated text);