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?

Magnus Hagander · Accepted Answer · 2010-09-17 10:21:19Z

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.

answered Sep 17, 2010 at 10:21

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DrColossos Over a year ago

+1 This is indeed the easiest (and posible quickest) approach

Nik · Accepted Answer · 2010-09-17 09:34:21Z

0

try

insert into db1.table1 select * from db2.table2

answered Sep 17, 2010 at 9:34

Nik

4,0653 gold badges23 silver badges16 bronze badges

Comments

Quassnoi · Accepted Answer · 2010-09-17 09:40:00Z

0

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.

edited Sep 17, 2010 at 9:40

answered Sep 17, 2010 at 9:34

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related