0

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

0

3 Answers 3

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.

1

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;
1
  • This is updating the current database by using data of a different database. Is it possible to update table in a different database (via dblink) by using data of the current database? In my case, I can use dblink in one database (current), but cannot use it in another (different) (because one has "trust" permissions active, which I cannot change). Mar 3, 2021 at 17:59
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.