Lately I’ve been doing a lot more data-driven work, including data analysis. From this work, I’ve needed to import and export a lot of data. Here is my best quick-and-dirty way to copy a table from one Postgres table to another. I frequently use this method to refresh my local environment with production data.

Of course, if you’re using a tool like pgAdmin, you can export data at the click of a button, but depending on what you plan to do with the data, that may not be what you want to do. Using the pgAdmin export will include all of the record IDs, so if you’re using this data to augment another database (or in my case, refresh), this might not be what you want. But if that doesn’t matter, using a built-in import/export tool is definitely the easiest solution.

Unfortunately this usually doesn’t work for me, so this is what I do instead:

  1. Export the prod data into a CSV
\copy (select columnA, columnB, columnC from tablename) to 'absolute/path/plus/file.csv' csv DELIMITER ',';

Make sure you include the absolute filepath, otherwise you’ll have a lot of trouble finding your csv. (This is the absolute path to your local machine.)

You can include any query above, so if you need to filter or sort in a certain way, adjust your query accordingly.

  1. Drop the data from my local database and reset the sequence

Make triple-sure you’re in the correct environment before you do this!!

truncate tablename;

select nextval('sequencenameforprimarykey');
alter sequence sequencename restart with 1;

select nextval('sequencenameforprimarykey');
select count(*) from tablename

After you truncate your table and reset your sequence, you can select each and see that you’re ready to start fresh with your prod data.

  1. Insert data from the CSV
\copy table_name(columnA, columnB, columnC) from 'absolute/path/plus/file.csv' csv DELIMITER ',';

Notice that the above command is basically just the inverse of the first \copy command in step 1.

Depending on how much data you’re copying over, this step might take a while. At the end, you’ll see something like COPY 137 in your terminal, which indicates the copy is complete and 137 records were copied into your table.

I’ll usually use one more select count(*) from tablename to see the total number of records in the table, as a sanity check.

A few gotchas

  1. Permission denied errors

If you used \COPY without root permissions, try again with \copy. The \COPY command has a slightly different signature and requires root access, but \copy will work regardless.

  1. “Cannot insert {type} into column”

An issue of incorrect data to column type. When this happens to me, it usually means I forgot to specify the columns in my \copy to command, or I specified them in the wrong order. If you are going to ignore the IDs when you move data from one place to another – which is a good idea – you must specify the columns in your \copy to command!

  1. “Missing data for column”

When this happens to me, it usually means I forgot to specify the delimiter to be a comma in my \copy to command. Check your CSV file and ensure that you’re using the correct delimiter for your data.

  1. “Duplicate key value violates unique constraint”

Most likely cause is that you copied your entire table, including ids, but forgot to drop your secondary table’s data and/or reset the sequence.

And that’s about it! At this point, I’ve copied production data into a local or sandbox environment where its safe to tinker with or even delete data. Just be careful and always, always, always make sure you’re not in production when tinkering!

Happy SQL-ing!