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 thoughts on “Importing data from a CSV file into a Postgres table

  • February 21, 2012 at 7:04 pm

    If using ogr2ogr you can also specify loading data into PostgreSQL with copy:

    PG_USE_COPY: This may be “YES” for using COPY for inserting data to Postgresql. COPY is less robust than INSERT, but significantly faster.

  • August 3, 2012 at 9:17 pm

    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?

    • August 3, 2012 at 10:11 pm

      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.


      • April 3, 2013 at 1:24 pm

        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.

  • June 24, 2014 at 12:25 pm

    i want to know procedure for import spread sheet from suse linux to postgres sql(pg admin)ans plz within today itself…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.