StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As Henrik wrote you can use dblink to connect remote database and fetch result. For example:</p> <pre><code>psql dbtest CREATE TABLE tblB (id serial, time integer); INSERT INTO tblB (time) VALUES (5000), (2000); psql postgres CREATE TABLE tblA (id serial, time integer); INSERT INTO tblA SELECT id, time FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB') AS t(id integer, time integer) WHERE time > 1000; TABLE tblA; id | time ----+------ 1 | 5000 2 | 2000 (2 rows) </code></pre> <p>PostgreSQL has <a href="http://www.postgresql.org/docs/current/static/datatype-pseudo.html" rel="noreferrer">record</a> pseudo-type (only for function's argument or result type), which allows you query data from another (unknown) table.</p> <p>Edit:</p> <p>You can make it as prepared statement if you want and it works as well:</p> <pre><code>PREPARE migrate_data (integer) AS INSERT INTO tblA SELECT id, time FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB') AS t(id integer, time integer) WHERE time > $1; EXECUTE migrate_data(1000); -- DEALLOCATE migrate_data; </code></pre> <p>Edit (yeah, another):</p> <p>I just saw your <a href="https://stackoverflow.com/questions/6086914/postgresql-insert-into-select-ii">revised question</a> (closed as duplicate, or just very similar to this).</p> <p>If my understanding is correct (postgres has tbla and dbtest has tblb and you want <strong>remote insert with local select</strong>, not <strong>remote select with local insert</strong> as above):</p> <pre><code>psql dbtest SELECT dblink_exec ( 'dbname=postgres', 'INSERT INTO tbla SELECT id, time FROM dblink ( ''dbname=dbtest'', ''SELECT id, time FROM tblb'' ) AS t(id integer, time integer) WHERE time > 1000;' ); </code></pre> <p>I don't like that nested dblink, but AFAIK I can't reference to tblB in <a href="http://www.postgresql.org/docs/current/static/contrib-dblink-exec.html" rel="noreferrer">dblink_exec</a> body. Use LIMIT to specify top 20 rows, but I think you need to sort them using ORDER BY clause first.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload