Transfer data between databases with PostgreSQL

sql postgresql insertion
Nicopuri picture Nicopuri · Jun 16, 2010 · Viewed 134.6k times · Source

I need to transfer some data from another database. The old database is called paw1.moviesDB and the new database is paw1. The schema of each table are the following.

Awards (name of the table)(new DB)
Id [PK] Serial           Award

Nominations (name of the table) (old DB)
Id [PK] Serial           nominations

How do I copy the data from old database to the new database?

Answer

Nate picture Nate · Mar 29, 2012

I just had to do this exact thing so I figured I'd post the recipe here. This assumes that both databases are on the same server.

First, copy the table from the old db to the new db. At the commandline:

pg_dump -U postgres -t <old_table> <old_database> | psql -U postgres -d <new_database>

Next, grant permissions of the copied table to the user of the new database. Log into psql:

psql -U postgres -d <new_database>

ALTER TABLE <old_table> OWNER TO <new_user>;

\q

At this point your copied table in your new database still has the name <old_table> from your old database. Assuming you want to move the data somewhere else, say to <new_table>, you can just use regular SQL queries:

INSERT INTO <new_table> (field1, field2, field3) 
SELECT field1, field2, field3 from <old_table>;

Done!

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More