Copy a table from one database to another in Postgres

postgresql copy database-table
nix picture nix · Jul 7, 2010 · Viewed 242k times · Source

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

Answer

thomax picture thomax · May 23, 2013

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 my_table my_db | psql target_db

Related questions

Copy a few of the columns of a csv file into a table

I have a CSV file with 10 columns. After creating a PostgreSQL table with 4 columns, I want to copy some of 10 columns into the table. the columns of my CSV table are like: x1 x2 x3 x4 x5 x6 x7 x8 …

Read More
The correct COPY command to load postgreSQL data from csv file that has single-quoted data?

I have csv file that has contents like this: 10,53073,0,0,'Y','2008-05-30 21:46:55',0,'2008-05-30 21:48:04',0,53071,2 I want to load the csv data into my_table. CREATE TABLE my_table ( ad_tree_id numeric(10,0) NOT NULL, node_id …

Read More
Copy in Postgres from a tab delimited file to table

I am working on a copy command to copy the contents of a tab delimited file with a header to a table in postgres. I have used copy before so I know how it works but I have a question …

Read More