Copy a table from one database to another in Postg

Question

I am trying to copy an entire table from one database to another in Postgres. Any suggestions?

Answer 1

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.

Answer 2

Using dblink would be more convenient!

truncate table tableA;

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

Answer 3

Use pg_dump to dump table data, and then restore it with psql.

Answer 4

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

pg_dump -t table_to_copy source_db | psql target_db

Answer 5

If the both DBs(from & to) are password protected, in that scenario terminal won't ask for the password for both the DBs, password prompt will appear only once. So, to fix this, pass the password along with the commands.

PGPASSWORD=<password> pg_dump -h <hostIpAddress> -U <hostDbUserName> -t <hostTable> > <hostDatabase> | PGPASSWORD=<pwd> psql -h <toHostIpAddress> -d <toDatabase> -U <toDbUser>

Answer 6

You could do the following:

pg_dump -h <host ip address> -U <host db user name> -t <host table> > <host database> | psql -h localhost -d <local database> -U <local db user>

---