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
    Permalink

    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.

    http://www.gdal.org/ogr/drv_pg.html

  • August 3, 2012 at 9:17 pm
    Permalink

    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
      Permalink

      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

      • April 3, 2013 at 1:24 pm
        Permalink

        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
    Permalink

    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.