postgresql INSERT INTO SELECT

Question

I m not sure if its standard SQL      INSERT INTO tblA    SELECT id  time      FROM tblB     WHERE time  gt  1000      What I m looking for is  what if tblA and tblB are in different DB Servers   Does PostgreSql gives any utility or has any functionality that will help to use INSERT query with PGresult struct    I mean SELECT id  time FROM tblB     will return a PGresult  on using PQexec  Is it possible to use this struct in another PQexec to execute an INSERT command     EDIT  If not possible then I would go for extracting the values from PQresult  and create a multiple INSERT statement syntax like   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 statement out of this

User · Answer

If you are looking for PERFORMANCE  give where condition inside the db link query  Otherwise it fetch all data from the foreign table and apply the where condition  INSERT INTO tblA  id time   SELECT id  time FROM  dblink  dbname dbname port 5432 host 10 10 90 190 user postgresuser password pass123     select id  time from tblB  where time gt      1000        AS t1 id integer  time integer

User · Answer

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    1 from   minisermao

User · Answer

insert into TABLENAMEA  A B C D   select A  integer B C D from TABLENAMEB

User · Answer

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

User · Answer

Here s an alternate solution  without using dblink   Suppose B represents the source database and A represents the target database  Then    Copy table from source DB to target DB   pg dump -t  lt source table gt   lt source db gt    psql  lt target db gt   Open psql prompt  connect to target db  and use a simple insert   psql    c  lt target db gt     INSERT INTO  lt target table gt  id  x  y  SELECT id  x  y FROM  lt source table gt    At the end  delete the copy of source table that you created in target table     DROP TABLE  lt source table gt

User · Answer

As Henrik wrote you can use dblink to connect remote database and fetch 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  gt  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  AS INSERT INTO tblA     SELECT id  time     FROM dblink  dbname dbtest    SELECT id  time FROM tblB       AS t id integer  time integer      WHERE time  gt   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 tbla         SELECT id  time         FROM dblink                         dbname dbtest                  SELECT id  time FROM tblb                     AS t id integer  time integer          WHERE time  gt  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

User · Answer

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  gt  1000

[sql] postgresql: INSERT INTO ... (SELECT * ...)

Examples related to sql

Examples related to postgresql

Examples related to insert

Examples related to dblink