Postgresql: Insert Into ... (select * ...)

I'm not sure if its standard SQL: INSERT INTO tblA (SELECT id, time FROM tblB WHERE time > 1000) What I'm looking for is: what if tblA and tblB are in different

Solution 1:

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

psql dbtest
CREATETABLE tblB (id serial, timeinteger);
INSERTINTO tblB (time) VALUES (5000), (2000);

psql postgres
CREATETABLE tblA (id serial, timeinteger);

INSERTINTO tblA
    SELECT id, timeFROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
    AS t(id integer, timeinteger)
    WHEREtime>1000;

TABLE tblA;
 id |time----+------1|50002|2000
(2rows)

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) ASINSERTINTO tblA
    SELECT id, timeFROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
    AS t(id integer, timeinteger)
    WHEREtime> $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 dbtest

SELECT dblink_exec
(
    'dbname=postgres','INSERT INTO tblaSELECT 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.

Solution 2:

If you want insert into specify column:

INSERTINTOtable (time)
(SELECTtimeFROM 
    dblink('dbname=dbtest', 'SELECT time FROM tblB') AS t(timeinteger) 
    WHEREtime>1000
);

Solution 3:

This notation (first seen here) looks useful too:

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

Solution 4:

You can use dblink to create a view that is resolved in another database. This database may be on another server.

Solution 5:

insertinto TABLENAMEA (A,B,C,D) 
select A::integer,B,C,D from TABLENAMEB