Instantly share code, notes, and snippets.

Embed
What would you like to do?
Importing and Exporting CSV files with PostgreSQL — First published in fullweb.io 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;
@nara-l

This comment has been minimized.

nara-l commented Apr 3, 2018

Works using \copy when you are not admin.

@adityanarkar

This comment has been minimized.

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?

@rsubr

This comment has been minimized.

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