Importing data from a CSV file into a Postgres table

Written by

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

Comments

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

February 21, 2012

Rastermanden

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

Log in to Reply
August 3, 2012

David

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?

Log in to Reply
1. August 3, 2012
  
  kostas
  
  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
  
  Log in to Reply
  1. April 3, 2013
    
    Alfons
    
    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.
    
    Log in to Reply
June 24, 2014

prathap

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

Log in to Reply