CODEWITHSUNDEEP
sqlpostgresql
Avadhesh
Avadhesh

Reputation: 4703

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

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?

Upvotes: 3

Views: 1645

Answers (3)

Magnus Hagander
Magnus Hagander

Reputation: 25078

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.

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425251

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.

Upvotes: 0

Nik
Nik

Reputation: 4075

try

insert into db1.table1 select * from db2.table2

Upvotes: 0

Related Questions