Skip to content

Instantly share code, notes, and snippets.

@nepsilon
Last active July 29, 2024 01:26
Show Gist options
  • Save nepsilon/f2937fe10fe8b0efc0cc to your computer and use it in GitHub Desktop.
Save nepsilon/f2937fe10fe8b0efc0cc to your computer and use it in GitHub Desktop.
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
Copy link

nara-l commented Apr 3, 2018

Works using \copy when you are not admin.

@adityanarkar
Copy link

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
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.

@Mkang1204
Copy link

Mkang1204 commented Feb 28, 2019

Thank you for sharing the queries. I think it should work but does't work on my computer. No matter how I change the file path(relative/absolute), I always get the error -

ERROR: relative path not allowed for COPY to file
SQL state: 42602

BTW, when I tried to use the DOWLOAD button in the API, it didn't download the right query result table but give me an error.csv file...

image

Here's what my sql
image
the query works - just got stuck on the exporting part. It would be great that someone share your experience on this. Thank you!

@dleesva
Copy link

dleesva commented Aug 22, 2019

Need help on how to define the path/file name of the CSV to be imported. I am using a full pathway from my local computer and running the \copy from the command line. (‘C:\Users\xxxxxxx\Documents\PostGresData\TestTable.csv’). Result is as follows:

‘C:\Users\xxxxxxx\Documents\PostGresData\TestTable.csv: "Invalid Argument"

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