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;
This comand will bulk insert all rows from the file into the table.
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).
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.0