Copy table data from one database to another

Question

I have two databases on the same server and need to copy data from a table in the first db to a table in the second. A few caveats:

Both tables already exist (ie: I must not drop the 'copy-to' table first. I need to just add the data to the existing table)
The column names differ. So I need to specify exactly which columns to copy, and what their names are in the new table

After some digging I have only been able to find this:

pg_dump -t tablename dbname | psql otherdbname

But the above command doesn't take into account the two caveats I listed.

Use a foreign data wrapper

user330315
– user330315

2016-06-02 11:42:06 +00:00
Commented Jun 2, 2016 at 11:42 — user330315
– user330315, Commented Jun 2, 2016 at 11:42

Nick Barnes · Accepted Answer · 2016-06-02 12:09:06Z

2

For a table t, with columns a and b in the source database, and x and y in the target:

psql -d sourcedb -c "copy t(a,b) to stdout" | psql -d targetdb -c "copy t(x,y) from stdin"

answered Jun 2, 2016 at 12:09

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

Comments

Dirk Trilsbeek · Accepted Answer · 2016-06-02 11:43:17Z

I'd use an ETL tool for this. There are free tools available, they can help you change column names and they are widely used and tested. Most tools allow external schedulers like the windows task scheduler or cron to run transformations based on whatever time schedule you need.

I personally have used Pentaho PDI for similar tasks in the past and it has always worked well for me. For your requirement I'd create a single transformation that first loads the table data from the source database, modify the column names in a "Select Values"-step and then insert the values into the target table using the "truncate" option to remove the existing rows from the target table. If your table is too big to be re-filled each time, you'd need to figure out a delta load procedure.

Collectives™ on Stack Overflow

Copy table data from one database to another

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related