how to update a column from another database?

Question

I would like to update a field that has been delete from the backup database. how can I update the field from an other database

Answer 1

If you want to write an SQL statement that affects tables from different databases, the best option is to use a postgres_fdw foreign table.

But perhaps it is simpler to extract the data from one database to a file using COPY and to load the file into the other database.

Answer 2

I solved I by using dblink

I created the extension

CREATE EXTENSION dblink;

then I used it as:

UPDATE my_table t SET  column_name=i.column_name, 
FROM  dblink('dbname=other_db','SELECT id, x_column_name FROM my_table') as i(id INT, column_name TEXT) where t.id=i.id;

Answer 3

m3asmi's way helped me but it was asking for database username & password in my windows machine so i tried this & worked like a charm!

CREATE EXTENSION dblink;

then:

UPDATE my_table t SET  column_name=i.column_name, 
FROM  dblink('dbname=other_db password=dbpassword user=dbuser','SELECT id, x_column_name FROM my_table') as i(id INT, column_name TEXT) where t.id=i.id;