postgresql: INSERT INTO ... (SELECT * ...)

As Henrik wrote you can use dblink to connect remote database and fetch result. For example:

psql dbtestCREATE TABLE tblB (id serial, time integer);INSERT INTO tblB (time) VALUES (5000), (2000);psql postgresCREATE 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)

PostgreSQL has record pseudo-type (only for function's argument or result type), which allows you query data from another (unknown) table.

Edit:

You can make it as prepared statement if you want and it works as well:

PREPARE migrate_data (integer) ASINSERT 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;

Edit (yeah, another):

I just saw your revised question (closed as duplicate, or just very similar to this).

If my understanding is correct (postgres has tbla and dbtest has tblb and you want remote insert with local select, not remote select with local insert as above):

psql dbtestSELECT 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;');

I don't like that nested dblink, but AFAIK I can't reference to tblB in dblink_exec body. Use LIMIT to specify top 20 rows, but I think you need to sort them using ORDER BY clause first.

If you want insert into specify column:

INSERT INTO table (time)(SELECT time FROM     dblink('dbname=dbtest', 'SELECT time FROM tblB') AS t(time integer)     WHERE time > 1000);

This notation (first seen here) looks useful too:

insert into postagem (  resumopostagem,  textopostagem,  dtliberacaopostagem,  idmediaimgpostagem,  idcatolico,  idminisermao,  idtipopostagem) select  resumominisermao,  textominisermao,  diaminisermao,  idmediaimgminisermao,  idcatolico ,  idminisermao,  1from  minisermao