title: is it possible to insert rows from a local table into a remote table in postgresql?
body: <p>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 <a href="https://stackoverflow.com/questions/21056451/remote-insert-using-local-select-in-postgres">here</a>, <a href="https://stackoverflow.com/questions/6083132/postgresql-insert-into-select">here</a> and <a href="https://stackoverflow.com/questions/12297402/postgresql-dblink-in-stored-functions-from-local-to-remote-database">here</a>, but I haven't yet found a satisfactory answer or anyone saying definitively that it's not possible).</p>
  
  <p>Specifically, assume I have tables remotetable and localtable, each with a single column columnA.  I can run this statement successfully:</p>
  
  <pre><code>select dblink_exec('myconnection', 'insert into remotetable (columnA) values (1);');
  </code></pre>
  
  <p>but what I want to do is this:</p>
  
  <pre><code>select dblink_exec('myconnection', 'insert into remotetable (columnA) select columnA from localtable;');
  </code></pre>
  
  <p>But this fails with: <em>relation "localtable" does not exist</em>, presumably because localtable does not exist on the remote database.</p>
  
  <p>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 <a href="http://www.postgresql.org/docs/9.1/static/contrib-dblink-exec.html" rel="nofollow noreferrer">dblink-exec</a> show inserts with static values, not with the results of a local query.</p>
  
  <p>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. </p>