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 psycopg2
.
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 remote_db
to local_db
first?