Instantly share code, notes, and snippets.

What would you like to do?
Importing and Exporting CSV files with PostgreSQL — First published in issue #19

Importing and exporting CSV files with PostgreSQL

Let’s see how to use PostgreSQL to import and export CSV files painlessly with the COPY command.

Import CSV into table t_words:

COPY t_words FROM '/path/to/file.csv' DELIMITER ',' CSV;

You can tell quote char with QUOTE and change delimiter with DELIMITER.

Import CSV into table t_words, telling what columns to use:

COPY t_words("name", "count", "def") FROM 'file.csv' DELIMITER ',' CSV; 

Export table to a CSV file:

COPY t_words TO 'file.csv' DELIMITER ',' CSV HEADER; 

Export custom results to a CSV file:

COPY (SELECT word, def FROM t_words) TO 'file.csv' CSV;

This comment has been minimized.

Copy link

nara-l commented Apr 3, 2018

Works using \copy when you are not admin.


This comment has been minimized.

Copy link

adityanarkar commented Apr 27, 2018

How to import csv file whose data contains delimiter character?

For example String name: "Jane, Doe"

How not to misunderstand the comma inside the string as a delimiter?


This comment has been minimized.

Copy link

rsubr commented May 22, 2018

It is required to quote your data if you have timestamp fields in your DB.

To export a table to CSV use this psql command with headers and quoting all fields with double-quote:

\COPY t_words TO 't_words.csv' DELIMITER ',' QUOTE '"' FORCE QUOTE * HEADER CSV

To import a CSV to DB table:

\COPY t_words FROM 't_words.csv' DELIMITER ',' QUOTE '"' HEADER CSV

COPY is very quick, I had to recently load 78 million rows of financial data from a 9GB CSV file. Using the Python pandas library it took 2 days to finish. On the same machine, writing to CSV from pandas and using copy took only 4 hours - 1 hour for ingest and 3 hours for creating indexes. Exporting to CSV was limited only by disk performance - 8 minutes in my case.

COPY to binary format produces larger files than CSV and offers similar performance.

Alternate solutions using pg_dump are cumbersome as you cannot restore to a different table. You will have to rename the original table, run pg_restore and then rename the tables again.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment