Welcome to End Point’s blog

Ongoing observations by End Point people

Copying Rows Between PostgreSQL Databases

A recurring question is: ‘how can I copy a couple of rows from one database to another’? People try to set up some replication, or dump entire database, however the solution is pretty simple.


For this blog post I will create two similar tables, I will be copying data from one to another. They are in the same database, but in fact that doesn’t matter, you can use this example to copy to another database as well. Even on another server, that’s enough to change arguments for the psql commands.

The tables are:

test=# CREATE TABLE original_table (i INTEGER, t TEXT);
test=# CREATE TABLE copy_table (i INTEGER, t TEXT);

Now I will insert two rows, which I will copy later to the “copy_table”.

test=# INSERT INTO original_table(i, t) VALUES
  (1, 'Lorem ipsum dolor sit amet'),
  (2, 'consectetur adipiscing elit');

test=# SELECT * FROM original_table ;
 i |              t              
 1 | Lorem ipsum dolor sit amet
 2 | consectetur adipiscing elit
(2 rows)

test=# SELECT * FROM copy_table;
 i | t 
(0 rows)

The Solution

Of course I can set up replication, which is too much effort for ad hoc copying two rows. Of course I could dump entire database, but imagine a database with millions of rows, and you just want to copy those two rows.

Fortunately there is “copy” command. However simple copy saves file on the same server as PostgreSQL is running, and usually you don’t have access to the file system there. There is another command, that’s internal command for psql, it is named “\copy”. It behaves exactly like copy, but it writes files on the machine you run psql at.

Save To File

The first and simplest solution we could save those two rows into a file, and load it later on another database.

First, let’s find out how “\copy” works:

$ psql test -c \
"\copy (SELECT i, t FROM original_table ORDER BY i) TO STDOUT"

1 Lorem ipsum dolor sit amet
2 consectetur adipiscing elit

As you can see, the main part of this command is the select query which allows to choose rows we want to export. You can provide there any “where” clause you want.

So now we can save it to a file:

$ psql test -c \
"\copy (SELECT i, t FROM original_table ORDER BY i) TO STDOUT" > /tmp/f.tsv

Loading now is also pretty easy with the same “\copy” command.

psql test -c "\copy copy_table (i, t) FROM STDIN"

Don’t Save to File

Saving to a file has one drawback: if the data amount is huge, then the file will be huge as well, it will waste disk space, and can be slower than using a pipe to load data. You can use a pipe to join the output of one psql command with input of another one. This is as simple as:

psql test -c \
"\copy (SELECT i, t FROM original_table ORDER BY i) TO STDOUT" | \
psql test -c "\copy copy_table (i, t) FROM STDIN"

test=# SELECT * FROM copy_table ;
 i |              t              
 1 | Lorem ipsum dolor sit amet
 2 | consectetur adipiscing elit
(2 rows)

As you can see, that’s much simpler than setting up replication or dumping whole database.


Brad Wallace said...

Excellent, a very useful. I was using a file intermediate, much less elegant than this.

Ivan Minčík said...

I recommend You to look at pg_comparator

Ryan M. Ferris said...

Thanks for this. I made this work from one database to another on Windows 7. I needed the -d 'databasename' argument after all the remote args. The trick was to first recreate the table with the exact schema from the first database on the second.

psql -h localhost -p port@ -U postgres -d WC2014_08_01_2014 -c "\copy (SELECT precincts42 FROM ld42) TO STDOUT" | psql -h localhost -p port@ -U postgres -d WC2014_09_05_2014 -c "\copy ld42 FROM STDIN"

krishna mohan said...

I succeeded in copying from one table to other in same database.
I am trying to copy from database in one machine to other and getting permission denied error. Please advise.

John Kaplan said...

Note the copy command will fail if the source/destination database tables have different columns or columns in different orders. You can fix this by putting the column names into the copy command in parentheses.

psql source_db -c "\copy source_table (column_name1, column_name2) from in.txt"

To automatically get the column names in order from the source database, do a pg_dump -t of the table, and copy/paste the column names out of the embedded COPY command output.