Using PostgreSQL's COPY to import & export CSV files

postgresql-copy-csv-import-export.md

Using PostgreSQL's COPY to import & export CSV files

COPY can read/write data not only from/to CSV, but also from/to binary files. For the import, the table must exist along with its columns matching CSV columns.

Export table to CSV

COPY <table> TO 'file.csv' DELIMITER ',' CSV HEADER; 

Export select results to CSV

COPY (SELECT <column1>,<column2> FROM <table>) TO '<file>.csv' CSV;

Export with explicit null character

COPY <table> TO 'file.csv' DELIMITER ',' CSV HEADER NULL as E''; 

Import CSV into table

COPY <table> FROM '<path>/<file>.csv' DELIMITER ',' CSV;

Import CSV into table with columns

COPY <table>("<column1>", "<column2>") FROM '<file>.csv' DELIMITER ',' CSV; 

Import CSV with explicit special character as delimiter

COPY <table> FROM '<file>.csv' DELIMITER E'\t' CSV; 

Import CSV by piping from STDIN

cat myfile | psql -c "COPY <table>  FROM STDIN"