How to import CSV file data into a PostgreSQL table?

Question

How can I write a stored procedure that imports data from a CSV file and populates the table?

Answer 1

Take a look at this short article.

---

Solution paraphrased here:

Create your table:

CREATE TABLE zip_codes  (ZIP char(5), LATITUDE double precision, LONGITUDE double precision,  CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar); 

Copy data from your CSV file to the table:

COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv); 

Answer 2

If you don't have permission to use COPY (which work on the db server), you can use \copy instead (which works in the db client). Using the same example as Bozhidar Batsov:

Create your table:

CREATE TABLE zip_codes  (ZIP char(5), LATITUDE double precision, LONGITUDE double precision,  CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar); 

Copy data from your CSV file to the table:

\copy zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV 

Mind that \copy ... must be written in one line and without a ; at the end!

You can also specify the columns to read:

\copy zip_codes(ZIP,CITY,STATE) FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV 

See the documentation for COPY:

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

and note:

For identity columns, the COPY FROM command will always write the column values provided in the input data, like the INSERT option OVERRIDING SYSTEM VALUE.