Copy data from table of one db into table of another db using query (both tables have same structure)

Question

I have two database in the same schema. My db is in Postgres. I want to copy data of any table (i.e product) of my 1st db into the same table of the 2nd db.

Is it possible to do so using query?

Answer 1

try

insert into db1.table1 select * from db2.table2

Answer 2

It's not possible in vanilla PostgreSQL installation.

If you are able to install contrib modules, use dblink:

INSERT
INTO    product
SELECT  *
FROM    dblink
        (
        'dbname=sourcedb',
        '
        SELECT  *
        FROM    product
        '
        ) AS p (id INT, column1 INT, column2 TEXT, …)

This should be run in the target database.

Answer 3

Can't do it as a single SQL command (at least not without dblink), but the easiest way is probably to just use a pipe between two psql's - use COPY on both ends, one sending the data out in CSV format the other one receiving it.