postgresql – Copy a table from one database to another in Postgres

postgresql – Copy a table from one database to another in Postgres

Extract the table and pipe it directly to the target database:

pg_dump -t table_to_copy source_db | psql target_db

Note: If the other database already has the table set up, you should use the -a flag to import data only, else you may see weird errors like Out of memory:

pg_dump -a -t table_to_copy source_db | psql target_db

You can also use the backup functionality in pgAdmin II. Just follow these steps:

  • In pgAdmin, right click the table you want to move, select Backup
  • Pick the directory for the output file and set Format to plain
  • Click the Dump Options #1 tab, check Only data or only Schema (depending on what you are doing)
  • Under the Queries section, click Use Column Inserts and User Insert Commands.
  • Click the Backup button. This outputs to a .backup file
  • Open this new file using notepad. You will see the insert scripts needed for the table/data. Copy and paste these into the new database sql page in pgAdmin. Run as pgScript – Query->Execute as pgScript F6

Works well and can do multiple tables at a time.

postgresql – Copy a table from one database to another in Postgres

Using dblink would be more convenient!

truncate table tableA;

insert into tableA
select *
from dblink(hostaddr=xxx.xxx.xxx.xxx dbname=mydb user=postgres,
            select a,b from tableA)
       as t1(a text,b text);

Leave a Reply

Your email address will not be published.