Importing and Exporting CSV files with PostgreSQL — First published in fullweb.io issue #19

postgres-import-export-csv.md

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;

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

Here's what my sql

the query works - just got stuck on the exporting part. It would be great that someone share your experience on this. Thank you!

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"