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

Question

I'm not sure its standard SQL:

INSERT INTO tblA (SELECT id, time FROM tblB WHERE time > 1000) 

I am looking for: what to do if tblA and tblB are on different database servers .

Does PostgreSql provide any utility or have any functions that will help to use INSERT query with PGresult struct

I mean SELECT id, time FROM tblB ... will return a PGresult* when using PQexec . Is it possible to use this structure in another PQexec to execute the INSERT command.

EDIT:
If this is not possible, I would like to extract the values ​​from PQresult * and create the syntax of several INSERT statements, for example:

 INSERT INTO films (code, title, did, date_prod, kind) VALUES ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); 

Is it possible to create a prepared expression from this !: (

Answer 1

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

 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) 

PostgreSQL has a pseudo-type record (only for function argument or result type) that allows you to query data from another (unknown) table.

Edit:

You can do this as a prepared statement if you want, and it also works:

 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; 

Edit (yes, other):

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 insert , and not remote select with local insert as above):

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

I do not like this nested dblink, but AFAIK I cannot reference tblB in the dblink_exec body. Use LIMIT to specify the top 20 lines, but I think you need to sort them using the ORDER BY clause.

Answer 2

If you want to insert into the options column:

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

Answer 3

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

Answer 4

This post (first seen here ) also looks useful:

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

Answer 5

Here's an alternative solution, without using dblink .

Suppose B represents the source database and A represents the target database: Then

1. Copy the table from the source database to the target database:

    pg_dump -t <source_table> <source_db> | psql <target_db> 

2. Open the psql prompt, connect to target_db and use a simple insert :

    psql # \c <target_db>; # INSERT INTO <target_table>(id, x, y) SELECT id, x, y FROM <source_table>; 

3. At the end, delete the copy of source_table that you created in target_table.

    # DROP TABLE <source_table>; 

Answer 6

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