Bulk Insert and Export Data with csv Files using Postgres copy Command

Bulk Insert and Export Data with csv Files using 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.

This is the format when using Postgres:

postgres=# copy <table name> <column names> from '<full file path to CSV file>' DELIMITER ',' CSV HEADER; 
COPY cmd

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

Bulk INSERT data with COPY cmd

The copy command makes this possible. Defining columns is optional, and renames the columns from the original file. If left out, HEADER will be pulled in from the imported file.

In this case, the DELIMITER defaults to tabs, but with CSV we need to choose commas (this works like a key value pair).

And CSV and HEADER are two separate options: CSV for the file type, HEADER tells the copy command that the first row in the file contains headers and shouldn’t be copied into the DB.

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

From TABLE to CSV file

0

Leave a Comment

Your email address will not be published. Required fields are marked *