[Postgres] Bulk Insert and Export Data with csv Files with Postgres copy Command

When working with databases, it seems inevitable that you will find yourself needing to export data from a table to send along to another team, company, or organization. Or vise versa, you have a file, possibly a csv, and you want to add all of it into your database. There are lots of options to do this but the easiest is by using the copy command in Postgres.

 

copy <table name> <column names> from '<full file path to CSV file>' DELIMITER ',' CSV HEADER; 

Ex:

copy users (first_name, last_name, email) from '/path/to/file/my-users.csv' DELIMITER ',' CSV HEADER

This comand will bulk insert all rows from the file into the table.

HEADER: is a boolean type, tell whether the first row of csv is header or not, to decide whether copy header into table or not.

 

 

If you change "from" to "to" you can export a copy of data to the file FROM the DB as a CSV.

copy users (first_name, last_name, email) to '/path/to/file/my-users-copy.csv' DELIMITER ',' CSV HEADER