Importing data from a CSV file into a Postgres table

Simple CSV file import

You have a CSV file called “data.csv”. It has a header line, and is delimited using “;”. You want to import it into Postgres and a table called “your_table”:

Create the database table. Set column-types so the string fields in the CSV file, can be cast to values in columns.

CREATE TABLE your_table
(
  -- Your columns
);

Execute COPY command:

COPY your_table FROM '/path/to/csv/file/data.csv' WITH DELIMITER ';' CSV HEADER;

If the data is geospatial

Another option is using ogr2ogr, which has a CSV driver. The COPY command could be faster.

If you have point data, check out this howto: Loading Point Data from a CSV File into PostGIS

5 Replies to “Importing data from a CSV file into a Postgres table”

  1. I am using pgAdmin III. All I want to do is import a small table from Excel into my PostgreSQL database. I saved the table as a CSV. How do I import this? I don’t even see where to type the code you posted above. I am very new to this. Why is such a basic thing so seemingly hard to execute?

    1. Hi David

      You execute it like a query. You can use PgAdmin III for that. Connect to a database that contains a table that matches your CSV file, and press the “SQL” button that gives you the SQL query editor and write the query there. Hope this helps.

      Kostas

      1. It doesn’t work in pgadmin at all. you can write stored procedure to do it and then select procedure() if you want to use pgadmin. Another way is to use psql – then you can write those queries directly.

Leave a Reply