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

Question

I have two databases in the same schema. My db is in Postgres. I want to copy data from any table (for example, a product) of my 1st db into the same table of the 2nd category.

Is it possible to do this with a query?

Answer 1

It is impossible to do this as a single SQL command (at least not without dblink), but the easiest way is probably just to use the channel between two psql - use COPY at both ends, one sends data in CSV format the other receives it.

Answer 2

to try

insert into db1.table1 select * from db2.table2

Answer 3

PostgreSQL.

Contrib, dblink:

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

.