Importing Exporting CSV Files in PostgreSQL Databases via COPY
This is going to be a short article which will illustrate importing and exporting a table from or to a csv file using PostgreSQL COPY command.
Importing a table from CSV
Assuming you already have a table in place with the right columns, the command is as follows
COPY FROM ‘/path/to/csv/SourceCSVFile.csv’ DELIMITERS ‘,’ CSV;
Exporting a CSV from a table.
COPY TO ‘/path/to/csv/TargetCSVFile’ DELIMITERS ‘,’ CSV;
Its important to mention here that generally if your data is in unicode or need strict Encoding, then Always set client_encoding before running any of the above mentioned commands.
To set CLIENT_ENCODING parameter in PostgreSQL
set client_encoding to ‘UTF8’
or set client_encoding to ’latin1’
Another thing to guard against is nulls, while exporting , if some fields are null then PostgreSQL will add ’/N’ to represent a null field, this is fine but may cause issues if you are trying to import that data in say SQL server.
A quick fix is modify the export command by specifying what would you prefer as a null placeholder in exported CSV
COPY TO ‘/path/to/csv/TargetCSVFile’ DELIMITERS ‘,’ NULL as E’’;
Another common requirement is import or export with the header.
Import CSV to table with Header for columns present in first row of csv file.
COPY FROM ‘/path/to/csv/SourceCSVFile.csv’ DELIMITERS ‘,’ CSV HEADER
Export a table to CSV with Headers present in the first row.
COPY TO ‘/path/to/csv/TargetCSVFile’ DELIMITERS ‘,’ CSV HEADER;
A complete list of all supported settings can be found here