0

I have two postgresql databases, one on my local machine and one on a remote machine. If I'm connected to the local database (with psql), how do I execute a statement that inserts rows into a table on the remote database by selecting rows from a table on the local database? (I've seen this asked a handful of times, like here, here and here, but I haven't yet found a satisfactory answer or anyone saying definitively that it's not possible).

Specifically, assume I have tables remotetable and localtable, each with a single column columnA. I can run this statement successfully:

select dblink_exec('myconnection', 'insert into remotetable (columnA) values (1);');

but what I want to do is this:

select dblink_exec('myconnection', 'insert into remotetable (columnA) select columnA from localtable;');

But this fails with: relation "localtable" does not exist, presumably because localtable does not exist on the remote database.

Is it possible to do what I'm trying to do? If so, how do I indicate that localtable is, in fact, local? All of the examples I've seen for dblink-exec show inserts with static values, not with the results of a local query.

Note: I know how to query data from a remote table and insert into a local table, but I'm trying to move data in the other direction.

Community
  • 1
  • 1
Mike W.
  • 131
  • 2
  • 9

1 Answers1

1

If so, how do I indicate that localtable is, in fact, local?

It's not possible because dblink acts as an SQL client to the remote server. That's why the queries sent through dblink_exec must be self-contained: they can do no more than any other query sent by any SQL application. Every object in the query is local to it from the server's perspective.

That is, unless you use another functionality, a Foreign-Data Wrapper with the postgres_fdw driver. This is a more sophisticated way to achieve server-to-server querying in which the SQL engine itself has this notion of foreign and local tables.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156