Database Hack: Copy and Paste Tables

Here's a hack for cutting and pasting the contents of one Postgresql table into another. It's useful for when you have a small table, and it's just a hassle to dump and restore from a file.

Step 1. Use the commands below to spill out the contents of a table in CSV format to your screen:

psql <Your_Source_Datatabase>
\copy <source_table> TO stdout CSV

Step 2. Highlight and copy the output from the command.

Step 3. Connect to your new database and load the table.

psql <Your_Dest_Datatabase>
\copy <dest_table> FROM stdin CSV
-- Paste the contents from above here
\.

The final \. is really important, it's what tells Postgres that you are done feeding it data.

And there you have it. You've copied the file in less time then it takes to read this blog entry.