I have two different Postgres databases on two different servers (one is actually local). Similar to this question, I would like to work with both databases at the same time. However, I can't figure out a way how to do that using
So I was thinking I probably need two different cursors:
conn_local = psycopg2.connect(dbname='local_db', host='localhost') conn_remote = psycopg2.connect(dbname='remote_db', host='some.other.server') curs_local = conn_local.cursor() curs_remote = conn_remote.cursor()
But how can I address those databases? For instance, when I try to join data from both tables:
curs_local.execute(""" CREATE TABLE local_db.public.newtable AS SELECT remote_db.public.remotetable.rcolumn AS col_from_remote, local_db.public.localtable.lcolumn AS col_from_local FROM remote_db.public.remotetable, local_db.public.localtable""")
There will be an error in the style of
psycopg2.NotSupportedError: cross-database references are not implemented: "local_db.public.new_table". The
ATTACH TABLE command (as described in the solution here) does apparently not exist in Postgres / psycopg2.
Is it possible to work with multiple databases at a time? How?
Or will I have to copy (export / import) the data from