PopSQL

How to Import a CSV into PostgreSQL using Copy

Importing a CSV into PostgreSQL requires you to create a table first. Duplicating an existing table's structure might be helpful here too.

The commands you need here are copy (executed server side) or \copy (executed client side). The former requires your database to be able to access the CSV file, which is rarely going to work for you in a production environment like Amazon RDS because you're not going to be uploading random CSV files to your database server. However, if you use psql on your local machine and you're connected to your remote database, you can use the latter \copy command, assuming your machine can access the CSV file.

In your terminal, let's open psql:

psql your_database_name # or postgres://username:password@amazonaws.com

Now it's time to use the \copy command:

-- Assuming you have already created an imported_users table
-- Assuming your CSV has no headers
\copy imported_users from 'imported_users.csv' csv;

-- If your CSV does have headers, they need to match the columns in your table
\copy imported_users from 'imported_users.csv' csv header;

-- If you want to only import certain columns
\copy imported_users (id, email) from 'imported_users.csv' csv header;
database icon
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and PostgreSQL